/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.hvtt.web.controller;

import com.hvtt.web.models.RPT_ThongKeKhacModel;
import com.hvtt.web.models.RPT_ThongKeNapTienModel;
import com.hvtt.web.models.RPT_ThongKeTaiKhoanModel;
import com.hvtt.web.models.RPT_TongCashoutModel;
import com.hvtt.web.models.RPT_TongDoanhThuModel;
import com.hvtt.web.models.RPT_TongDoanhThuTheoGameModel;
import com.hvtt.web.models.RPT_TongTienNapModel;
import com.hvtt.web.utilities.ConstClass;
import com.hvtt.web.utilities.Utilities;
import java.awt.Color;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.concurrent.ConcurrentNavigableMap;
import java.util.concurrent.ConcurrentSkipListMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.faces.context.ExternalContext;
import javax.faces.context.FacesContext;
import javax.servlet.ServletContext;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.RandomStringUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.json.simple.JSONObject;
import org.primefaces.model.DefaultStreamedContent;
import org.primefaces.model.StreamedContent;

/**
 *
 * @author Administrator
 */
public class ReportEcxelController implements Serializable {

    private CellStyle createBorderedStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        style.setBorderRight(CellStyle.BORDER_THIN);
//        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
//        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
//        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
//        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        return style;
    }

    enum NewStyle {

        TitleStyle, TitleDate, HeaderStyle, GroupSummaryText, GroupSummaryNumberic, GroupSummaryMoney, CellText, CellTextCenter, CellNumberic, CellMoney
    }

    private ConcurrentNavigableMap<NewStyle, CellStyle> initMapStyle(Workbook wb) {
        ConcurrentNavigableMap<NewStyle, CellStyle> styleMap = new ConcurrentSkipListMap();

        Font fontTitleStyle12 = wb.createFont();
        fontTitleStyle12.setFontHeightInPoints((short) 15);
        fontTitleStyle12.setFontName("Times New Roman");
        fontTitleStyle12.setBoldweight(Font.BOLDWEIGHT_BOLD);
        CellStyle styleTitleStyle12 = wb.createCellStyle();
        styleTitleStyle12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleTitleStyle12.setAlignment(CellStyle.ALIGN_CENTER);
        styleTitleStyle12.setFont(fontTitleStyle12);
        styleMap.put(NewStyle.TitleStyle, styleTitleStyle12);

        Font fontTitleDate12 = wb.createFont();
        fontTitleDate12.setFontHeightInPoints((short) 13);
        fontTitleDate12.setFontName("Times New Roman");
        fontTitleDate12.setBoldweight(Font.BOLDWEIGHT_BOLD);
        CellStyle styleTitleDate12 = wb.createCellStyle();
        styleTitleDate12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleTitleDate12.setAlignment(CellStyle.ALIGN_CENTER);
        styleTitleDate12.setFont(fontTitleDate12);
        styleMap.put(NewStyle.TitleDate, styleTitleDate12);

        Font fontHeaderStyle12 = wb.createFont();
        fontHeaderStyle12.setFontHeightInPoints((short) 12);
        fontHeaderStyle12.setFontName("Times New Roman");
        fontHeaderStyle12.setColor(IndexedColors.WHITE.getIndex());
        fontHeaderStyle12.setBoldweight(Font.BOLDWEIGHT_BOLD);
        CellStyle styleHeaderStyle12 = createBorderedStyle(wb);
        styleHeaderStyle12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleHeaderStyle12.setAlignment(CellStyle.ALIGN_CENTER);
        //styleHeaderStyle12.setFillForegroundColor(new XSSFColor(Color.decode("#3498DB")).getIndexed());//
        styleHeaderStyle12.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        styleHeaderStyle12.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styleHeaderStyle12.setFont(fontHeaderStyle12);
        styleMap.put(NewStyle.HeaderStyle, styleHeaderStyle12);

        Font fontGroupSummaryStyle12 = wb.createFont();
        fontGroupSummaryStyle12.setFontHeightInPoints((short) 12);
        fontGroupSummaryStyle12.setFontName("Times New Roman");
        fontGroupSummaryStyle12.setBoldweight(Font.BOLDWEIGHT_BOLD);
        CellStyle styleGroupSummaryStyle12 = createBorderedStyle(wb);
        styleGroupSummaryStyle12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleGroupSummaryStyle12.setAlignment(CellStyle.ALIGN_LEFT);
        styleGroupSummaryStyle12.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        styleGroupSummaryStyle12.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styleGroupSummaryStyle12.setFont(fontGroupSummaryStyle12);
        styleMap.put(NewStyle.GroupSummaryText, styleGroupSummaryStyle12);

        Font fontGroupSummaryNumberic12 = wb.createFont();
        fontGroupSummaryNumberic12.setFontHeightInPoints((short) 12);
        fontGroupSummaryNumberic12.setFontName("Times New Roman");
        CellStyle styleGroupSummaryNumberic12 = createBorderedStyle(wb);
        styleGroupSummaryNumberic12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleGroupSummaryNumberic12.setAlignment(CellStyle.ALIGN_RIGHT);
        styleGroupSummaryNumberic12.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        styleGroupSummaryNumberic12.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styleGroupSummaryNumberic12.setFont(fontGroupSummaryNumberic12);
        styleMap.put(NewStyle.GroupSummaryNumberic, styleGroupSummaryNumberic12);

        Font fontGroupSummaryMoney12 = wb.createFont();
        fontGroupSummaryMoney12.setFontHeightInPoints((short) 12);
        fontGroupSummaryMoney12.setFontName("Times New Roman");
        fontGroupSummaryMoney12.setBoldweight(Font.BOLDWEIGHT_BOLD);
        CellStyle styleGroupSummaryMoney12 = createBorderedStyle(wb);
        styleGroupSummaryMoney12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleGroupSummaryMoney12.setAlignment(CellStyle.ALIGN_RIGHT);
        styleGroupSummaryMoney12.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        styleGroupSummaryMoney12.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styleGroupSummaryMoney12.setFont(fontGroupSummaryMoney12);
        styleMap.put(NewStyle.GroupSummaryMoney, styleGroupSummaryMoney12);

        Font fontCellText12 = wb.createFont();
        fontCellText12.setFontHeightInPoints((short) 12);
        fontCellText12.setFontName("Times New Roman");
        CellStyle styleCellText12 = createBorderedStyle(wb);
        styleCellText12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleCellText12.setAlignment(CellStyle.ALIGN_LEFT);
        styleCellText12.setFont(fontCellText12);
        styleMap.put(NewStyle.CellText, styleCellText12);

        Font fontCellTexCentert12 = wb.createFont();
        fontCellTexCentert12.setFontHeightInPoints((short) 12);
        fontCellTexCentert12.setFontName("Times New Roman");
        CellStyle styleCellTextCenter12 = createBorderedStyle(wb);
        styleCellTextCenter12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleCellTextCenter12.setAlignment(CellStyle.ALIGN_CENTER);
        styleCellTextCenter12.setFont(fontCellTexCentert12);
        styleMap.put(NewStyle.CellTextCenter, styleCellTextCenter12);

        Font fontCellNumberic12 = wb.createFont();
        fontCellNumberic12.setFontHeightInPoints((short) 12);
        fontCellNumberic12.setFontName("Times New Roman");
        CellStyle styleCellNumberic12 = createBorderedStyle(wb);
        styleCellNumberic12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleCellNumberic12.setAlignment(CellStyle.ALIGN_RIGHT);
        styleCellNumberic12.setFont(fontCellNumberic12);
        styleMap.put(NewStyle.CellNumberic, styleCellNumberic12);

        Font fontCellMoney12 = wb.createFont();
        fontCellMoney12.setFontHeightInPoints((short) 12);
        fontCellMoney12.setFontName("Times New Roman");
        CellStyle styleCellMoney12 = createBorderedStyle(wb);
        styleCellMoney12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleCellMoney12.setAlignment(CellStyle.ALIGN_RIGHT);
        styleCellMoney12.setFont(fontCellMoney12);
        styleMap.put(NewStyle.CellMoney, styleCellMoney12);



        return styleMap;
    }

    enum Style {

        DefaultBold16, DefaultBold13, DefaultNormal12, MoneyDefaultNormal12, DefaultBold10, BGFooter,
        DefaultNormal10Center, DefaultNormal10Left, HeaderCenterText, EmptyNguoiChamDiem
    }

    private ConcurrentNavigableMap<Style, CellStyle> createMapStyle(Workbook wb) {
        ConcurrentNavigableMap<Style, CellStyle> tmpMap = new ConcurrentSkipListMap();
        Font fontDefaultBold16 = wb.createFont();
        fontDefaultBold16.setFontHeightInPoints((short) 16);
        fontDefaultBold16.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fontDefaultBold16.setFontName("Times New Roman");
        CellStyle styleDefaultBold16 = wb.createCellStyle();
        styleDefaultBold16.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDefaultBold16.setAlignment(CellStyle.ALIGN_CENTER);
        styleDefaultBold16.setFont(fontDefaultBold16);
        tmpMap.put(Style.DefaultBold16, styleDefaultBold16);

        Font fontDefaultBold13 = wb.createFont();
        fontDefaultBold13.setFontHeightInPoints((short) 13);
        fontDefaultBold13.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fontDefaultBold13.setFontName("Times New Roman");
        CellStyle styleDefaultBold13 = wb.createCellStyle();
        styleDefaultBold13.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDefaultBold13.setAlignment(CellStyle.ALIGN_CENTER);
        styleDefaultBold13.setFont(fontDefaultBold13);
        tmpMap.put(Style.DefaultBold13, styleDefaultBold13);

        Font fontDefaultNormal12 = wb.createFont();
        fontDefaultNormal12.setFontHeightInPoints((short) 12);
        fontDefaultNormal12.setFontName("Times New Roman");
        CellStyle styleDefaultNormal12 = createBorderedStyle(wb);
        styleDefaultNormal12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDefaultNormal12.setAlignment(CellStyle.ALIGN_LEFT);
        styleDefaultNormal12.setFont(fontDefaultNormal12);
        tmpMap.put(Style.DefaultNormal12, styleDefaultNormal12);

        Font fontMoneyDefaultNormal12 = wb.createFont();
        fontMoneyDefaultNormal12.setFontHeightInPoints((short) 12);
        fontMoneyDefaultNormal12.setFontName("Times New Roman");
        CellStyle styleMoneyDefaultNormal12 = createBorderedStyle(wb);
        styleMoneyDefaultNormal12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleMoneyDefaultNormal12.setAlignment(CellStyle.ALIGN_RIGHT);
        styleMoneyDefaultNormal12.setFont(fontMoneyDefaultNormal12);
        tmpMap.put(Style.MoneyDefaultNormal12, styleMoneyDefaultNormal12);

        Font fontDefaultHeaderBold10 = wb.createFont();
        fontDefaultHeaderBold10.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fontDefaultHeaderBold10.setFontHeightInPoints((short) 10);
        fontDefaultHeaderBold10.setFontName("Times New Roman");
        CellStyle styleDefaultBold10 = createBorderedStyle(wb);
        styleDefaultBold10.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDefaultBold10.setAlignment(CellStyle.ALIGN_CENTER);
        styleDefaultBold10.setFont(fontDefaultHeaderBold10);
        tmpMap.put(Style.DefaultBold10, styleDefaultBold10);

        Font fontDefaultNormal10 = wb.createFont();
        fontDefaultNormal10.setFontHeightInPoints((short) 10);
        fontDefaultNormal10.setFontName("Times New Roman");
        CellStyle styleDefaultNormal10Center = createBorderedStyle(wb);
        styleDefaultNormal10Center.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDefaultNormal10Center.setAlignment(CellStyle.ALIGN_CENTER);
        styleDefaultNormal10Center.setFont(fontDefaultNormal10);
        tmpMap.put(Style.DefaultNormal10Center, styleDefaultNormal10Center);

        CellStyle styleDefaultNormal10Left = createBorderedStyle(wb);
        styleDefaultNormal10Left.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleDefaultNormal10Left.setAlignment(CellStyle.ALIGN_LEFT);
        styleDefaultNormal10Left.setFont(fontDefaultNormal10);
        tmpMap.put(Style.DefaultNormal10Left, styleDefaultNormal10Left);


        Font fontNewDefaultNormal12 = wb.createFont();
        fontNewDefaultNormal12.setFontHeightInPoints((short) 14);
        fontNewDefaultNormal12.setFontName("Times New Roman");
        fontNewDefaultNormal12.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fontNewDefaultNormal12.setColor(IndexedColors.WHITE.getIndex());

        CellStyle stylePhongBanLeft = createBorderedStyle(wb);
        stylePhongBanLeft.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        stylePhongBanLeft.setAlignment(CellStyle.ALIGN_LEFT);
        stylePhongBanLeft.setFillForegroundColor(new XSSFColor(Color.decode("#3498DB")).getIndexed());
        stylePhongBanLeft.setFillPattern(CellStyle.SOLID_FOREGROUND);
        stylePhongBanLeft.setFont(fontNewDefaultNormal12);
        tmpMap.put(Style.HeaderCenterText, stylePhongBanLeft);


        CellStyle styleCellEmptyNguoiChamDiem = createBorderedStyle(wb);
        styleCellEmptyNguoiChamDiem.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleCellEmptyNguoiChamDiem.setAlignment(CellStyle.ALIGN_LEFT);
        styleCellEmptyNguoiChamDiem.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
        styleCellEmptyNguoiChamDiem.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styleCellEmptyNguoiChamDiem.setFont(fontNewDefaultNormal12);
        tmpMap.put(Style.EmptyNguoiChamDiem, styleCellEmptyNguoiChamDiem);

        CellStyle styleBGFooter = createBorderedStyle(wb);
        styleBGFooter.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styleBGFooter.setAlignment(CellStyle.ALIGN_CENTER);
        styleBGFooter.setFillForegroundColor(new XSSFColor(Color.decode("#4765A0")).getIndexed());
        styleBGFooter.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styleBGFooter.setFont(fontNewDefaultNormal12);
        tmpMap.put(Style.BGFooter, styleBGFooter);

        return tmpMap;
    }

    public StreamedContent thongKeNapTienToExcel(Date tuNgay, Date denNgay, List<RPT_ThongKeNapTienModel> lst) {
       try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Thống Kê Số Tài Khoản Nạp");

            Row row;
            Cell cell;

            ConcurrentNavigableMap<NewStyle, CellStyle> styles = initMapStyle(wb);

            Row rowReportTitle = sheet.createRow((short) 0);
            rowReportTitle.setHeightInPoints(30);
            cell = rowReportTitle.createCell(0);
            cell.setCellValue("Thống Kê Số Tài Khoản Nạp");
            cell.setCellStyle(styles.get(NewStyle.TitleStyle));
            mergeColspanNoBoder(cell, 4);


            Row rowHeader1 = sheet.createRow((short) 1);
            rowHeader1.setHeightInPoints(20);
            cell = rowHeader1.createCell(0);
            cell.setCellValue(String.format("Từ ngày %s - Đến ngày %s", Utilities.parseDateTime(tuNgay, "dd/MM/yyyy"), Utilities.parseDateTime(denNgay, "dd/MM/yyyy")));
            cell.setCellStyle(styles.get(NewStyle.TitleDate));
            mergeColspanNoBoder(cell, 4);

            row = sheet.createRow(4);

            cell = row.createCell(0);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("CP Name/HĐH");
            mergeRowspan(cell, 1);

            cell = row.createCell(1);
            cell.setCellValue("Tài khoản");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(2);
            cell.setCellValue("Tên game");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
              cell = row.createCell(3);
            cell.setCellValue("Số tiền");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(4);
            cell.setCellValue("Thời Gian");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

          


            
            
            
            int lastRow;
            int rowIndex = 6;
            
         
            
            String strCPID;
            
          
            int iTongCong=0;
            long lTongTien=0;
            int i=0;
            while(i<lst.size())
            {
                RPT_ThongKeNapTienModel item = lst.get(i);
                strCPID=item.getCpid();
                boolean blnFlag_CPID=false;
                int iTongCP=0;
                long lTongTienCP=0;
                int iGroup_CPID=rowIndex+1;
                while (i < lst.size() && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))) {
                    if(!blnFlag_CPID)
                    {
                        row=sheet.createRow(rowIndex);
                        lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_CPID, lastRow);
                        sheet.setRowSumsBelow(false);

                        cell = row.createCell(0);
                        cell.setCellValue(lst.get(i).getCpName());
                        //mergeColspan(cell, 2);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(2);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(3);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(4);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        rowIndex++;
                    }
                    blnFlag_CPID=true;
                   
                    
                    
                    String strHDH=lst.get(i).getHdh();
                    boolean blnFlag_HDH=false;
                    int iGroup_HDH=rowIndex+1;
                    int iHDH=0;
                    long lTongTienHDH=0;
                     while (i < lst.size() && (lst.get(i).getHdh()== null ? strHDH== null : lst.get(i).getHdh().equals(strHDH))
                             && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))
                             ) {
                         
                            if(!blnFlag_HDH)
                            {
                                row=sheet.createRow(rowIndex);
                                lastRow = sheet.getLastRowNum();
                                sheet.groupRow(iGroup_HDH, lastRow);
                                sheet.setRowSumsBelow(false);

                                cell = row.createCell(0);
                                cell.setCellValue(lst.get(i).getHdh());
                               // mergeColspan(cell, 2);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                                
                                
                                cell = row.createCell(1);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

                                cell = row.createCell(2);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

                                cell = row.createCell(3);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

                                cell = row.createCell(4);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                                rowIndex++;
                            }
                          blnFlag_HDH=true;
                         
                       
                        
                        
                        if(lst.get(i).getUserName().length()>=4)
                        {
                            lst.get(i).setUserName(lst.get(i).getUserName().substring(0, 4)+"****");
                        }
                        row = sheet.createRow(rowIndex);
                        cell = row.createCell(0);
                        cell.setCellStyle(styles.get(NewStyle.CellText));
                        cell.setCellValue("");
                        
                        cell = row.createCell(1);
                        cell.setCellStyle(styles.get(NewStyle.CellText));
                        cell.setCellValue(lst.get(i).getUserName());
                        
                         cell = row.createCell(2);
                        cell.setCellStyle(styles.get(NewStyle.CellText));
                        cell.setCellValue(lst.get(i).getGame());
                        
                        cell = row.createCell(3);
                        cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                        cell.setCellValue(lst.get(i).getSoTien());
                        
                        cell = row.createCell(4);
                        cell.setCellStyle(styles.get(NewStyle.CellText));
                        cell.setCellValue(Utilities.parseDateTime(lst.get(i).getThoiGian(), "dd/MM/yyyy"));
                        
                        
                        

                          lTongTien+=lst.get(i).getSoTien();
                          lTongTienHDH+=lst.get(i).getSoTien();
                          lTongTienCP+=lst.get(i).getSoTien();
                        
                         iHDH++;
                         iTongCP++;
                         iTongCong++;
                         
                          rowIndex++;
                         i++;
                     }
                     lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_HDH, lastRow);
                     sheet.getRow(iGroup_HDH-1).getCell(1).setCellValue(iHDH);
                     sheet.getRow(iGroup_HDH-1).getCell(1).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     sheet.getRow(iGroup_HDH-1).getCell(3).setCellValue(lTongTienHDH);
                     sheet.getRow(iGroup_HDH-1).getCell(3).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                     
                }
                 lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_CPID, lastRow);
                     sheet.getRow(iGroup_CPID-1).getCell(1).setCellValue(iTongCP);
                     sheet.getRow(iGroup_CPID-1).getCell(1).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     sheet.getRow(iGroup_CPID-1).getCell(3).setCellValue(lTongTienCP);
                     sheet.getRow(iGroup_CPID-1).getCell(3).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            }
            
            lastRow = sheet.getLastRowNum();
            lastRow++;
            row = sheet.createRow(lastRow);
            
            cell = row.createCell(0);
           // mergeColspan(cell, 2);
            cell.setCellStyle(styles.get(NewStyle.CellText));
            cell.setCellValue("Tổng cộng");
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

            cell = row.createCell(1);
            cell.setCellStyle(styles.get(NewStyle.CellNumberic));
            cell.setCellValue(iTongCong);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            
            cell = row.createCell(2);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            
            cell = row.createCell(3);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(lTongTien);
             
            cell = row.createCell(4);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            
            sheet.setColumnWidth(0, 256 * 25);
            sheet.setColumnWidth(1, 256 * 25);
            sheet.setColumnWidth(2, 256 * 25);
            sheet.setColumnWidth(3, 256 * 20);
            sheet.setColumnWidth(4, 256 * 20);
          
           Calendar cal=Calendar.getInstance();
            String fileName = "thongkenaptien_" + Utilities.parseDateTime(cal.getTime(), "dd_MM_yyyy") + ".xls";
            File f = new File(getLocalPath() + File.separator + fileName);
            try (FileOutputStream fileOut = FileUtils.openOutputStream(f, true)) {
                wb.write(fileOut);
                fileOut.close();
            }
            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext())
                    .getResourceAsStream(getUrlUploadFiles() + "/" + fileName);
            String contentType = FacesContext.getCurrentInstance().getExternalContext().getMimeType(getLocalPath() + File.separator + fileName);
            return new DefaultStreamedContent(stream, contentType, fileName);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public StreamedContent thongKeTaiKhoanToExcel(Date tuNgay, Date denNgay, List<RPT_ThongKeTaiKhoanModel> lst) {
        try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Thống Kê Tài Khoản");

            Row row;
            Cell cell;

            ConcurrentNavigableMap<NewStyle, CellStyle> styles = initMapStyle(wb);

            Row rowReportTitle = sheet.createRow((short) 0);
            rowReportTitle.setHeightInPoints(30);
            cell = rowReportTitle.createCell(0);
            cell.setCellValue("Thống Kê Tài Khoản");
            cell.setCellStyle(styles.get(NewStyle.TitleStyle));
            mergeColspanNoBoder(cell, 5);


            Row rowHeader1 = sheet.createRow((short) 1);
            rowHeader1.setHeightInPoints(20);
            cell = rowHeader1.createCell(0);
            cell.setCellValue(String.format("Từ ngày %s - Đến ngày %s", Utilities.parseDateTime(tuNgay, "dd/MM/yyyy"), Utilities.parseDateTime(denNgay, "dd/MM/yyyy")));
            cell.setCellStyle(styles.get(NewStyle.TitleDate));
            mergeColspanNoBoder(cell, 5);

            row = sheet.createRow(4);

            cell = row.createCell(0);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("CP Name");
            mergeRowspan(cell, 1);

            cell = row.createCell(1);
            cell.setCellValue("Tên Game");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(2);
            cell.setCellValue("Hệ Điều Hành");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(3);
            cell.setCellValue("Thời Gian");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(4);
            cell.setCellValue("Số Tài Khoản");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(5);
            cell.setCellValue("Tổng");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            int startRow = 6;
            int tmpStartRow = 6;
            int countAcc = 0;
            double totalOfGroup = 0;
            int rowOfGroup = 0;
            int lastRow;
            int rowIndex = 0;
            int stt = 1;
            String cpId = "";

            for (int i = 0; i < lst.size(); i++) {

                RPT_ThongKeTaiKhoanModel item = lst.get(i);
                if (!cpId.equalsIgnoreCase(item.getCpid())) {
                    if (!cpId.isEmpty()) {
                        lastRow = sheet.getLastRowNum();

                        sheet.groupRow(rowIndex + 1, lastRow);
                        sheet.setRowSumsBelow(false);
                        //set tong cua group
                        row = sheet.getRow(rowIndex);
                        row.getCell(row.getLastCellNum() - 1).setCellValue(Utilities.formatNumber("###,###.##", totalOfGroup));
                        //tao neu row fist of group
                        row = sheet.createRow(startRow);
                        cell = row.createCell(0);
                        cell.setCellValue(item.getCpName());
                        mergeColspan(cell, 3);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

                        cell = row.createCell(4);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        cell = row.createCell(5);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryMoney));

                        totalOfGroup = 0;
                        //tmpStartRow = lastRow + 2;

                    } else {
                        //start first row
                        row = sheet.createRow(startRow);
                        cell = row.createCell(0);
                        cell.setCellValue(item.getCpName());
                        mergeColspan(cell, 3);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

                        cell = row.createCell(4);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        cell = row.createCell(5);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryMoney));

                    }
                    //reset rơ index
                    rowIndex = sheet.getLastRowNum();
                    stt = 1;
                    startRow++;
                    cpId = item.getCpid();
                }
                row = sheet.createRow(startRow);
                cell = row.createCell(0);
                cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                cell.setCellValue(stt);
                cell = row.createCell(1);
                cell.setCellStyle(styles.get(NewStyle.CellText));
                cell.setCellValue(item.getGame());
                cell = row.createCell(2);
                cell.setCellStyle(styles.get(NewStyle.CellText));
                cell.setCellValue(item.getHdh());
                cell = row.createCell(3);
                cell.setCellStyle(styles.get(NewStyle.CellTextCenter));
                cell.setCellValue(Utilities.parseDateTime(item.getNgay(), "dd/MM/yyyy"));
                cell = row.createCell(4);
                cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                cell.setCellValue(item.getSoTK());
                cell = row.createCell(5);
                cell.setCellStyle(styles.get(NewStyle.CellNumberic));
//                cell.setCellValue(Utilities.parseDateTime(item.getThoiGian(), "dd/MM/yyyy"));
                stt++;
                startRow++;
                totalOfGroup += item.getSoTK();
                if (i == lst.size() - 1) {
                    lastRow = sheet.getLastRowNum();
                    sheet.groupRow(rowIndex + 1, lastRow);
                    sheet.setRowSumsBelow(false);
                    row = sheet.getRow(rowIndex);
                    row.getCell(row.getLastCellNum() - 1).setCellValue(Utilities.formatNumber("###,###.##", totalOfGroup));
                }
            }

            sheet.setColumnWidth(0, 256 * 25);
            sheet.setColumnWidth(1, 256 * 25);
            sheet.setColumnWidth(2, 256 * 25);
            sheet.setColumnWidth(3, 256 * 20);
            sheet.setColumnWidth(4, 256 * 20);
            sheet.setColumnWidth(5, 256 * 20);
            String fileName = RandomStringUtils.randomAlphanumeric(10) + ".xls";
            File f = new File(getLocalPath() + File.separator + fileName);
            try (FileOutputStream fileOut = FileUtils.openOutputStream(f, true)) {
                wb.write(fileOut);
                fileOut.close();
            }
            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext())
                    .getResourceAsStream(getUrlUploadFiles() + "/" + fileName);
            String contentType = FacesContext.getCurrentInstance().getExternalContext().getMimeType(getLocalPath() + File.separator + fileName);
            return new DefaultStreamedContent(stream, contentType, fileName);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
     public StreamedContent thongKeTaiKhoanToExcel1(Date tuNgay, Date denNgay, List<RPT_ThongKeTaiKhoanModel> lst) {
        try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Thống Kê Tài Khoản");

            Row row;
            Cell cell;

            ConcurrentNavigableMap<NewStyle, CellStyle> styles = initMapStyle(wb);

            Row rowReportTitle = sheet.createRow((short) 0);
            rowReportTitle.setHeightInPoints(30);
            cell = rowReportTitle.createCell(0);
            cell.setCellValue("Thống Kê Tài Khoản");
            cell.setCellStyle(styles.get(NewStyle.TitleStyle));
            mergeColspanNoBoder(cell, 3);


            Row rowHeader1 = sheet.createRow((short) 1);
            rowHeader1.setHeightInPoints(20);
            cell = rowHeader1.createCell(0);
            cell.setCellValue(String.format("Từ ngày %s - Đến ngày %s", Utilities.parseDateTime(tuNgay, "dd/MM/yyyy"), Utilities.parseDateTime(denNgay, "dd/MM/yyyy")));
            cell.setCellStyle(styles.get(NewStyle.TitleDate));
            mergeColspanNoBoder(cell, 3);

            row = sheet.createRow(4);

            cell = row.createCell(0);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("CP Name/HĐH");
            mergeRowspan(cell, 1);

            cell = row.createCell(1);
            cell.setCellValue("Tài khoản");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

//            cell = row.createCell(2);
//            cell.setCellValue("Hệ Điều Hành");
//            mergeRowspan(cell, 1);
//            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(2);
            cell.setCellValue("Thời Gian");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(3);
            cell.setCellValue("Số Tài Khoản");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));


            
            
            
            int lastRow;
            int rowIndex = 6;
            
         
            
            String strCPID;
            
          
            int iTongCong=0;
            int i=0;
            while(i<lst.size())
            {
                RPT_ThongKeTaiKhoanModel item = lst.get(i);
                strCPID=item.getCpid();
                boolean blnFlag_CPID=false;
                int iTongCP=0;
                int iGroup_CPID=rowIndex+1;
                while (i < lst.size() && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))) {
                    if(!blnFlag_CPID)
                    {
                        row=sheet.createRow(rowIndex);
                        lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_CPID, lastRow);
                        sheet.setRowSumsBelow(false);

                        cell = row.createCell(0);
                        cell.setCellValue(lst.get(i).getCpName());
                        mergeColspan(cell, 2);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        cell = row.createCell(3);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        rowIndex++;
                    }
                    blnFlag_CPID=true;
                   
                    
                    
                    String strHDH=lst.get(i).getHdh();
                    boolean blnFlag_HDH=false;
                    int iGroup_HDH=rowIndex+1;
                    int iHDH=0;
                     while (i < lst.size() && (lst.get(i).getHdh()== null ? strHDH== null : lst.get(i).getHdh().equals(strHDH))
                             && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))
                             ) {
                         
                            if(!blnFlag_HDH)
                            {
                                row=sheet.createRow(rowIndex);
                                lastRow = sheet.getLastRowNum();
                                sheet.groupRow(iGroup_HDH, lastRow);
                                sheet.setRowSumsBelow(false);

                                cell = row.createCell(0);
                                cell.setCellValue(lst.get(i).getHdh());
                                mergeColspan(cell, 2);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                                cell = row.createCell(3);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                                rowIndex++;
                            }
                          blnFlag_HDH=true;
                         
                       
                        
                        
                        if(lst.get(i).getUsername().length()>=4)
                        {
                            lst.get(i).setUsername(lst.get(i).getUsername().substring(0, 4)+"****");
                        }
                        row = sheet.createRow(rowIndex);
                        cell = row.createCell(0);
                        cell.setCellStyle(styles.get(NewStyle.CellText));
                        cell.setCellValue("");
                        
                        cell = row.createCell(1);
                        cell.setCellStyle(styles.get(NewStyle.CellText));
                        cell.setCellValue(lst.get(i).getUsername());
                        
                        cell = row.createCell(2);
                        cell.setCellStyle(styles.get(NewStyle.CellText));
                        cell.setCellValue(Utilities.parseDateTime(lst.get(i).getNgay(), "dd/MM/yyyy"));
                        
                        cell = row.createCell(3);
                        cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                        cell.setCellValue(lst.get(i).getSoTK());

                          
                         rowIndex++;
                         iHDH+=lst.get(i).getSoTK();
                         iTongCP+=lst.get(i).getSoTK();
                         iTongCong+=lst.get(i).getSoTK();
                         
                         i++;
                     }
                     lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_HDH, lastRow);
                     sheet.getRow(iGroup_HDH-1).getCell(3).setCellValue(iHDH);
                     sheet.getRow(iGroup_HDH-1).getCell(3).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                }
                 lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_CPID, lastRow);
                     sheet.getRow(iGroup_CPID-1).getCell(3).setCellValue(iTongCP);
                     sheet.getRow(iGroup_CPID-1).getCell(3).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            }
            
            lastRow = sheet.getLastRowNum();
            lastRow++;
            row = sheet.createRow(lastRow);
            cell = row.createCell(0);
            mergeColspan(cell, 2);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            cell.setCellValue("Tổng cộng");
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

            cell = row.createCell(3);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(iTongCong);
            //cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            
            sheet.setColumnWidth(0, 256 * 25);
            sheet.setColumnWidth(1, 256 * 25);
            sheet.setColumnWidth(2, 256 * 25);
            sheet.setColumnWidth(3, 256 * 20);
            
            Calendar cal=Calendar.getInstance();
            String fileName = "thongkedangky_" + Utilities.parseDateTime(cal.getTime(), "dd_MM_yyyy") + ".xls";
            File f = new File(getLocalPath() + File.separator + fileName);
            try (FileOutputStream fileOut = FileUtils.openOutputStream(f, true)) {
                wb.write(fileOut);
                fileOut.close();
            }
            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext())
                    .getResourceAsStream(getUrlUploadFiles() + "/" + fileName);
            String contentType = FacesContext.getCurrentInstance().getExternalContext().getMimeType(getLocalPath() + File.separator + fileName);
            return new DefaultStreamedContent(stream, contentType, fileName);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    private void thongTongTienNapToExcel_TheoHDH(Workbook wb,List<RPT_TongTienNapModel> lst,Date tuNgay, Date denNgay)
    {
      
            Sheet sheet = wb.createSheet("Thống Kê Nạp Thẻ Theo HĐH");

            Row row;
            Cell cell;

            ConcurrentNavigableMap<NewStyle, CellStyle> styles = initMapStyle(wb);

            Row rowReportTitle = sheet.createRow((short) 0);
            rowReportTitle.setHeightInPoints(30);
            cell = rowReportTitle.createCell(0);
            cell.setCellValue("Thống Kê Nạp Thẻ Theo HĐH");
            cell.setCellStyle(styles.get(NewStyle.TitleStyle));
            mergeColspanNoBoder(cell, 3);


            Row rowHeader1 = sheet.createRow((short) 1);
            rowHeader1.setHeightInPoints(20);
            cell = rowHeader1.createCell(0);
            cell.setCellValue(String.format("Từ ngày %s - Đến ngày %s", Utilities.parseDateTime(tuNgay, "dd/MM/yyyy"), Utilities.parseDateTime(denNgay, "dd/MM/yyyy")));
            cell.setCellStyle(styles.get(NewStyle.TitleDate));
            mergeColspanNoBoder(cell, 3);

            row = sheet.createRow(4);

            cell = row.createCell(0);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("CP Name/HĐH");
            mergeRowspan(cell, 1);

            cell = row.createCell(1);
            cell.setCellValue("Tên Game");
           mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

//            cell = row.createCell(2);
//            cell.setCellValue("Hệ Điều Hành");
//            mergeRowspan(cell, 1);
//            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

          
            cell = row.createCell(2);
            cell.setCellValue("Số tiền");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));


            
            
            
            int lastRow;
            int rowIndex = 6;
            
         
            
            String strCPID;
            
          
            int iTongCong=0;
            int i=0;
            while(i<lst.size())
            {
                RPT_TongTienNapModel item = lst.get(i);
                strCPID=item.getCpid();
                boolean blnFlag_CPID=false;
                int iTongCP=0;
                int iGroup_CPID=rowIndex+1;
                while (i < lst.size() && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))) {
                    if(!blnFlag_CPID)
                    {
                        row=sheet.createRow(rowIndex);
                        lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_CPID, lastRow);
                        sheet.setRowSumsBelow(false);

                        cell = row.createCell(0);
                        cell.setCellValue(lst.get(i).getCpName());
                        mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        cell = row.createCell(2);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        rowIndex++;
                    }
                    blnFlag_CPID=true;
                   
                    
                    
                    String strHDH=lst.get(i).getHdh();
                    boolean blnFlag_HDH=false;
                    int iGroup_HDH=rowIndex+1;
                    int iHDH=0;
                     while (i < lst.size() && (lst.get(i).getHdh()== null ? strHDH== null : lst.get(i).getHdh().equals(strHDH))
                             && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))
                             ) {
                         
                            if(!blnFlag_HDH)
                            {
                                row=sheet.createRow(rowIndex);
                                lastRow = sheet.getLastRowNum();
                                sheet.groupRow(iGroup_HDH, lastRow);
                                sheet.setRowSumsBelow(false);

                                cell = row.createCell(0);
                                cell.setCellValue(lst.get(i).getHdh());
                                mergeColspan(cell, 1);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                                cell = row.createCell(2);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                                rowIndex++;
                            }
                          blnFlag_HDH=true;
                         
                       
                        
                        
                       
                        row = sheet.createRow(rowIndex);
                        cell = row.createCell(0);
                        cell.setCellStyle(styles.get(NewStyle.CellText));
                        cell.setCellValue("");
                        
                        cell = row.createCell(1);
                        cell.setCellStyle(styles.get(NewStyle.CellText));
                        cell.setCellValue(lst.get(i).getGame());
                        
                        
                        cell = row.createCell(2);
                        cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                        cell.setCellValue(lst.get(i).getSoTien());

                          
                         rowIndex++;
                         iHDH+=lst.get(i).getSoTien();
                         iTongCP+=lst.get(i).getSoTien();
                         iTongCong+=lst.get(i).getSoTien();
                         
                         i++;
                     }
                     lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_HDH, lastRow);
                     sheet.getRow(iGroup_HDH-1).getCell(2).setCellValue(iHDH);
                     sheet.getRow(iGroup_HDH-1).getCell(2).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                }
                 lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_CPID, lastRow);
                     sheet.getRow(iGroup_CPID-1).getCell(2).setCellValue(iTongCP);
                     sheet.getRow(iGroup_CPID-1).getCell(2).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            }
            
            lastRow = sheet.getLastRowNum();
            lastRow++;
            row = sheet.createRow(lastRow);
            cell = row.createCell(0);
            mergeColspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            cell.setCellValue("Tổng cộng");
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

            cell = row.createCell(2);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(iTongCong);
            //cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            
            sheet.setColumnWidth(0, 256 * 25);
            sheet.setColumnWidth(1, 256 * 25);
            sheet.setColumnWidth(2, 256 * 25);

      
        //return null;
    }
    public StreamedContent thongTongTienNapToExcel(Date tuNgay, Date denNgay, List<RPT_TongTienNapModel> lst1, List<RPT_TongTienNapModel> lst2) {
        try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Thống Kê Nạp Thẻ");

            Row row;
            Cell cell;

            ConcurrentNavigableMap<NewStyle, CellStyle> styles = initMapStyle(wb);

            Row rowReportTitle = sheet.createRow((short) 0);
            rowReportTitle.setHeightInPoints(30);
            cell = rowReportTitle.createCell(0);
            cell.setCellValue("Thống Kê Nạp Thẻ");
            cell.setCellStyle(styles.get(NewStyle.TitleStyle));
            mergeColspanNoBoder(cell, 3);


            Row rowHeader1 = sheet.createRow((short) 1);
            rowHeader1.setHeightInPoints(20);
            cell = rowHeader1.createCell(0);
            cell.setCellValue(String.format("Từ ngày %s - Đến ngày %s", Utilities.parseDateTime(tuNgay, "dd/MM/yyyy"), Utilities.parseDateTime(denNgay, "dd/MM/yyyy")));
            cell.setCellStyle(styles.get(NewStyle.TitleDate));
            mergeColspanNoBoder(cell, 3);

            row = sheet.createRow(4);

            cell = row.createCell(0);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("CP Name/Gate/Telco");
            mergeRowspan(cell, 1);

            cell = row.createCell(1);
            cell.setCellValue("Mệnh giá");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

//            cell = row.createCell(2);
//            cell.setCellValue("Hệ Điều Hành");
//            mergeRowspan(cell, 1);
//            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(2);
            cell.setCellValue("Số lượng");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(3);
            cell.setCellValue("Tổng cộng");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));


            
            
            
            int lastRow;
            int rowIndex = 6;
            
         
            
            String strCPID;
            
          
            int iTongSoLuong=0;
            int iTongThanhTien=0;
            int i=0;
            while(i<lst1.size())
            {
                RPT_TongTienNapModel item = lst1.get(i);
                strCPID=item.getCpid();
                boolean blnFlag_CPID=false;
                int iSoLuong_CP=0;
                int iThanhTien_CP=0;
                int iGroup_CPID=rowIndex+1;
                while (i < lst1.size() && (lst1.get(i).getCpid() == null ? strCPID == null : lst1.get(i).getCpid().equals(strCPID))) {
                    if(!blnFlag_CPID)
                    {
                        row=sheet.createRow(rowIndex);
                        lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_CPID, lastRow);
                        sheet.setRowSumsBelow(false);

                        cell = row.createCell(0);
                        cell.setCellValue(lst1.get(i).getCpName());
                        mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(2);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(3);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        rowIndex++;
                    }
                    blnFlag_CPID=true;
                   
                    
                    
                    String strCongTT=lst1.get(i).getCongTT();
                    boolean blnFlag_CongTT=false;
                    int iGroup_CongTT=rowIndex+1;
                    int iSoLuong_CongTT=0;
                    int iThanhTien_CongTT=0;
                    
                     while (i < lst1.size() && (lst1.get(i).getCongTT()== null ? strCongTT== null : lst1.get(i).getCongTT().equals(strCongTT))
                             && (lst1.get(i).getCpid() == null ? strCPID == null : lst1.get(i).getCpid().equals(strCPID))
                             ) {
                         
                            if(!blnFlag_CongTT)
                            {
                                row=sheet.createRow(rowIndex);
                                lastRow = sheet.getLastRowNum();
                                sheet.groupRow(iGroup_CongTT, lastRow);
                                sheet.setRowSumsBelow(false);

                                cell = row.createCell(0);
                                cell.setCellValue(lst1.get(i).getCongTT());
                                mergeColspan(cell, 1);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                                
                                cell = row.createCell(2);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                                
                                cell = row.createCell(3);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                                rowIndex++;
                            }
                          blnFlag_CongTT=true;
                         
                          
                    String strTeleco=lst1.get(i).getTelco();
                    boolean blnFlag_Teleco=false;
                    int iGroup_Teleco=rowIndex+1;
                    int iSoLuong_Teleco=0;
                    int iThanhTien_Teleco=0;
                    
                     while (i < lst1.size() && (lst1.get(i).getCongTT()== null ? strCongTT== null : lst1.get(i).getCongTT().equals(strCongTT))
                             && (lst1.get(i).getCpid() == null ? strCPID == null : lst1.get(i).getCpid().equals(strCPID))
                             && (lst1.get(i).getTelco()== null ? strTeleco == null : lst1.get(i).getTelco().equals(strTeleco))
                             ) {
                         
                                if(!blnFlag_Teleco)
                                {
                                    row=sheet.createRow(rowIndex);
                                    lastRow = sheet.getLastRowNum();
                                    sheet.groupRow(iGroup_Teleco, lastRow);
                                    sheet.setRowSumsBelow(false);

                                    cell = row.createCell(0);
                                    cell.setCellValue(lst1.get(i).getTelco());
                                    mergeColspan(cell, 1);
                                    cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

                                    cell = row.createCell(2);
                                    cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

                                    cell = row.createCell(3);
                                    cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                                    rowIndex++;
                                }
                              blnFlag_Teleco=true;





    //                        if(lst1.get(i).getUser.length()>=4)
    //                        {
    //                            lst.get(i).setUsername(lst.get(i).getUsername().substring(0, 4)+"****");
    //                        }
                            row = sheet.createRow(rowIndex);
                            cell = row.createCell(0);
                            cell.setCellStyle(styles.get(NewStyle.CellText));
                            cell.setCellValue("");

                            cell = row.createCell(1);
                            cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                            cell.setCellValue(lst1.get(i).getSoTien());

                            cell = row.createCell(2);
                            cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                            cell.setCellValue(lst1.get(i).getSoLuong());

                            cell = row.createCell(3);
                            cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                            cell.setCellValue(lst1.get(i).getSoTien()*lst1.get(i).getSoLuong());


                             rowIndex++;
                             iSoLuong_CongTT+=lst1.get(i).getSoLuong();
                             iSoLuong_CP+=lst1.get(i).getSoLuong();
                             iSoLuong_Teleco+=lst1.get(i).getSoLuong();

                             iThanhTien_CP+=lst1.get(i).getSoLuong()*lst1.get(i).getSoTien();
                             iThanhTien_CongTT+=lst1.get(i).getSoLuong()*lst1.get(i).getSoTien();
                             iThanhTien_Teleco+=lst1.get(i).getSoLuong()*lst1.get(i).getSoTien();

                              iTongSoLuong+=lst1.get(i).getSoLuong();
                             iTongThanhTien+=lst1.get(i).getSoLuong()*lst1.get(i).getSoTien();

    //                         iTongCP+=lst1.get(i).getSoTK();
    //                         iTongCong+=lst1.get(i).getSoTK();

                             i++;
                         }
                       
                        lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_Teleco, lastRow);
                        sheet.getRow(iGroup_Teleco-1).getCell(2).setCellValue(iSoLuong_Teleco);
                        sheet.getRow(iGroup_Teleco-1).getCell(2).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));

                        sheet.getRow(iGroup_Teleco-1).getCell(3).setCellValue(iThanhTien_Teleco);
                        sheet.getRow(iGroup_Teleco-1).getCell(3).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                     }
                     lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_CongTT, lastRow);
                     sheet.getRow(iGroup_CongTT-1).getCell(2).setCellValue(iSoLuong_CongTT);
                     sheet.getRow(iGroup_CongTT-1).getCell(2).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                      sheet.getRow(iGroup_CongTT-1).getCell(3).setCellValue(iThanhTien_CongTT);
                     sheet.getRow(iGroup_CongTT-1).getCell(3).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                }
                 lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_CPID, lastRow);
                     sheet.getRow(iGroup_CPID-1).getCell(2).setCellValue(iSoLuong_CP);
                     sheet.getRow(iGroup_CPID-1).getCell(2).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                      sheet.getRow(iGroup_CPID-1).getCell(3).setCellValue(iThanhTien_CP);
                     sheet.getRow(iGroup_CPID-1).getCell(3).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            }
            
            lastRow = sheet.getLastRowNum();
            lastRow++;
            row = sheet.createRow(lastRow);
            cell = row.createCell(0);
            mergeColspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            cell.setCellValue("Tổng cộng");
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

            cell = row.createCell(2);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(iTongSoLuong);
            
            cell = row.createCell(3);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(iTongThanhTien);
            //cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            
            sheet.setColumnWidth(0, 256 * 25);
            sheet.setColumnWidth(1, 256 * 25);
            sheet.setColumnWidth(2, 256 * 25);
            sheet.setColumnWidth(3, 256 * 20);
            
            thongTongTienNapToExcel_TheoHDH(wb,lst2,tuNgay,denNgay);
            Calendar cal=Calendar.getInstance();
            String fileName = "thongkenapthe_" + Utilities.parseDateTime(cal.getTime(), "dd_MM_yyyy") + ".xls";
            File f = new File(getLocalPath() + File.separator + fileName);
            try (FileOutputStream fileOut = FileUtils.openOutputStream(f, true)) {
                wb.write(fileOut);
                fileOut.close();
            }
            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext())
                    .getResourceAsStream(getUrlUploadFiles() + "/" + fileName);
            String contentType = FacesContext.getCurrentInstance().getExternalContext().getMimeType(getLocalPath() + File.separator + fileName);
            return new DefaultStreamedContent(stream, contentType, fileName);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public StreamedContent tongTienCashOutTongToExcel(Date tuNgay, Date denNgay, List<RPT_TongCashoutModel> lst) {
        try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Tổng Cashout");

            Row row;
            Cell cell;

            ConcurrentNavigableMap<NewStyle, CellStyle> styles = initMapStyle(wb);

            Row rowReportTitle = sheet.createRow((short) 0);
            rowReportTitle.setHeightInPoints(30);
            cell = rowReportTitle.createCell(0);
            cell.setCellValue("Thống Kê Tổng Cashout");
            cell.setCellStyle(styles.get(NewStyle.TitleStyle));
            mergeColspanNoBoder(cell, 2);


            Row rowHeader1 = sheet.createRow((short) 1);
            rowHeader1.setHeightInPoints(20);
            cell = rowHeader1.createCell(0);
            cell.setCellValue(String.format("Từ ngày %s - Đến ngày %s", Utilities.parseDateTime(tuNgay, "dd/MM/yyyy"), Utilities.parseDateTime(denNgay, "dd/MM/yyyy")));
            cell.setCellStyle(styles.get(NewStyle.TitleDate));
            mergeColspanNoBoder(cell, 2);

            row = sheet.createRow(4);

            cell = row.createCell(0);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("CP Name");
            mergeRowspan(cell, 1);

            cell = row.createCell(1);
            cell.setCellValue("Telecom");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(2);
            cell.setCellValue("Số Tiền");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));


            int startRow = 6;
            int tmpStartRow = 6;
            int countAcc = 0;
            double totalOfGroup = 0;
            int rowOfGroup = 0;
            int lastRow;
            int rowIndex = 0;
            int stt = 1;
            String cpId = "";

            for (int i = 0; i < lst.size(); i++) {

                RPT_TongCashoutModel item = lst.get(i);

                if (!cpId.equalsIgnoreCase(item.getCpid())) {
                    if (!cpId.isEmpty()) {
                        lastRow = sheet.getLastRowNum();

                        sheet.groupRow(rowIndex + 1, lastRow);
                        sheet.setRowSumsBelow(false);
                        //set tong cua group
                        row = sheet.getRow(rowIndex);
                        row.getCell(row.getLastCellNum() - 1).setCellValue(Utilities.formatNumber("###,###.##", totalOfGroup));
                        //tao neu row fist of group
                        row = sheet.createRow(startRow);
                        cell = row.createCell(0);
                        cell.setCellValue(item.getCpName());
                        mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

                        cell = row.createCell(2);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryMoney));

                        totalOfGroup = 0;
                        //tmpStartRow = lastRow + 2;

                    } else {
                        //start first row
                        row = sheet.createRow(startRow);
                        cell = row.createCell(0);
                        cell.setCellValue(item.getCpName());
                        mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

                        cell = row.createCell(2);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryMoney));

                    }
                    //reset rơ index
                    rowIndex = sheet.getLastRowNum();
                    stt = 1;
                    startRow++;
                    cpId = item.getCpid();
                }
                row = sheet.createRow(startRow);
                cell = row.createCell(0);
                cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                cell.setCellValue(stt);
                cell = row.createCell(1);
                cell.setCellStyle(styles.get(NewStyle.CellText));
                cell.setCellValue(item.getTelco());
                cell = row.createCell(2);
                cell.setCellStyle(styles.get(NewStyle.CellMoney));
                cell.setCellValue(Utilities.formatNumber("###,###.##", item.getSoTien()));
                stt++;
                startRow++;
                totalOfGroup += item.getSoTien();
                if (i == lst.size() - 1) {//cac row con lai
                    lastRow = sheet.getLastRowNum();
                    sheet.groupRow(rowIndex + 1, lastRow);
                    sheet.setRowSumsBelow(false);
                    row = sheet.getRow(rowIndex);
                    row.getCell(row.getLastCellNum() - 1).setCellValue(Utilities.formatNumber("###,###.##", totalOfGroup));
                }
            }

            sheet.setColumnWidth(0, 256 * 25);
            sheet.setColumnWidth(1, 256 * 25);
            sheet.setColumnWidth(2, 256 * 25);
            String fileName = RandomStringUtils.randomAlphanumeric(10) + ".xls";
            File f = new File(getLocalPath() + File.separator + fileName);
            try (FileOutputStream fileOut = FileUtils.openOutputStream(f, true)) {
                wb.write(fileOut);
                fileOut.close();
            }
            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext())
                    .getResourceAsStream(getUrlUploadFiles() + "/" + fileName);
            String contentType = FacesContext.getCurrentInstance().getExternalContext().getMimeType(getLocalPath() + File.separator + fileName);
            return new DefaultStreamedContent(stream, contentType, fileName);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    public StreamedContent thongDoanhThuTheoGameToExcel(Date tuNgay, Date denNgay, List<RPT_TongDoanhThuTheoGameModel> lst) {
         try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Thống Kê Doanh Thu Theo Game");

            Row row;
            Cell cell;

            ConcurrentNavigableMap<NewStyle, CellStyle> styles = initMapStyle(wb);

            Row rowReportTitle = sheet.createRow((short) 0);
            rowReportTitle.setHeightInPoints(30);
            cell = rowReportTitle.createCell(0);
            cell.setCellValue("Thống Kê Doanh Thu Theo Game");
            cell.setCellStyle(styles.get(NewStyle.TitleStyle));
            mergeColspanNoBoder(cell, 5);


            Row rowHeader1 = sheet.createRow((short) 1);
            rowHeader1.setHeightInPoints(20);
            cell = rowHeader1.createCell(0);
            cell.setCellValue(String.format("Từ ngày %s - Đến ngày %s", Utilities.parseDateTime(tuNgay, "dd/MM/yyyy"), Utilities.parseDateTime(denNgay, "dd/MM/yyyy")));
            cell.setCellStyle(styles.get(NewStyle.TitleDate));
            mergeColspanNoBoder(cell, 5);

            row = sheet.createRow(4);

            cell = row.createCell(0);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("CP Name/Game");
            mergeRowspan(cell, 1);

            cell = row.createCell(1);
            cell.setCellValue("Số người chơi");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));


            cell = row.createCell(2);
            cell.setCellValue("Unique User");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(3);
            cell.setCellValue("Tổng số BIT user được");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
             cell = row.createCell(4);
            cell.setCellValue("% commission BIT");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
            cell = row.createCell(5);
            cell.setCellValue("ARPU");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
            cell = row.createCell(6);
            cell.setCellValue("Thời gian");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));


            
            
            
            int lastRow;
            int rowIndex = 6;
            
         
            
            String strCPID;
            
          
             int iSoNguoiChoi=0;
            int iSoUserUnique=0;
            long iTongSoBit=0;
            double dblPTBit=0;
            double dblARPU=0;
                
            int i=0;
            while(i<lst.size())
            {
                RPT_TongDoanhThuTheoGameModel item = lst.get(i);
                strCPID=item.getCpid();
                boolean blnFlag_CPID=false;
                int iSoNguoiChoi_CPID=0;
                int iSoUserUnique_CPID=0;
                long iTongSoBit_CPID=0;
                double dblPTBit_CPID=0;
                double dblARPU_CPID=0;
                
                int iGroup_CPID=rowIndex+1;
                while (i < lst.size() && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))) {
                    if(!blnFlag_CPID)
                    {
                        row=sheet.createRow(rowIndex);
                        lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_CPID, lastRow);
                        sheet.setRowSumsBelow(false);

                        cell = row.createCell(0);
                        cell.setCellValue(lst.get(i).getCpName());
                       // mergeColspan(cell, 2);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        cell = row.createCell(2);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        cell = row.createCell(3);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        cell = row.createCell(4);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        cell = row.createCell(5);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        cell = row.createCell(6);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        rowIndex++;
                    }
                    blnFlag_CPID=true;
                   
                    
                    
                  
                     row = sheet.createRow(rowIndex);
                        cell = row.createCell(0);
                        cell.setCellStyle(styles.get(NewStyle.CellText));
                        cell.setCellValue(lst.get(i).getGameName());
                        
                        cell = row.createCell(1);
                        cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                        cell.setCellValue(lst.get(i).getSoNguoiChoi());
                        
                        cell = row.createCell(2);
                        cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                        cell.setCellValue(lst.get(i).getSoNguoiChoiDistinct());
                        
                         cell = row.createCell(3);
                        cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                        cell.setCellValue(lst.get(i).getTongSoBit());
                        
                        cell = row.createCell(4);
                        cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                        cell.setCellValue(lst.get(i).getTongSoBit()*2/100);
                        
                        cell = row.createCell(5);
                        cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                        cell.setCellValue(lst.get(i).getArpu());
                        
                        cell = row.createCell(6);
                        cell.setCellStyle(styles.get(NewStyle.CellText));
                        cell.setCellValue(Utilities.parseDateTime(lst.get(i).getNgay(), "dd/MM/yyyy"));

                        double iARPU=(lst.get(i).getTongSoBit() * 2 / 100)/(lst.get(i).getSoNguoiChoiDistinct()<=0?1:lst.get(i).getSoNguoiChoiDistinct());
                        
                         rowIndex++;
                         
                         iSoNguoiChoi+=lst.get(i).getSoNguoiChoi();
                         iSoUserUnique+=lst.get(i).getSoNguoiChoiDistinct();
                         iTongSoBit+=lst.get(i).getTongSoBit();
                         dblARPU+=iARPU;
                         dblPTBit+=lst.get(i).getTongSoBit()*2/100;
                         
                         iSoNguoiChoi_CPID+=lst.get(i).getSoNguoiChoi();
                         iSoUserUnique_CPID+=lst.get(i).getSoNguoiChoiDistinct();
                         iTongSoBit_CPID+=lst.get(i).getTongSoBit();
                         dblARPU_CPID+=iARPU;
                         dblPTBit_CPID+=lst.get(i).getTongSoBit()*2/100;
                         
                         
                         
                         i++;
                     
                }
                 lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_CPID, lastRow);
                     
                     sheet.getRow(iGroup_CPID-1).getCell(1).setCellValue(iSoNguoiChoi_CPID);
                     sheet.getRow(iGroup_CPID-1).getCell(1).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                     sheet.getRow(iGroup_CPID-1).getCell(2).setCellValue(iSoUserUnique_CPID);
                     sheet.getRow(iGroup_CPID-1).getCell(2).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                     sheet.getRow(iGroup_CPID-1).getCell(3).setCellValue(iTongSoBit_CPID);
                     sheet.getRow(iGroup_CPID-1).getCell(3).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                     sheet.getRow(iGroup_CPID-1).getCell(4).setCellValue(dblPTBit);
                     sheet.getRow(iGroup_CPID-1).getCell(4).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                     sheet.getRow(iGroup_CPID-1).getCell(5).setCellValue(dblARPU);
                     sheet.getRow(iGroup_CPID-1).getCell(5).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                     sheet.getRow(iGroup_CPID-1).getCell(6).setCellValue("");
                     sheet.getRow(iGroup_CPID-1).getCell(6).setCellStyle(styles.get(NewStyle.GroupSummaryText));
            }
            
            lastRow = sheet.getLastRowNum();
            lastRow++;
            row = sheet.createRow(lastRow);
            
            cell = row.createCell(0);
           // mergeColspan(cell, 2);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            cell.setCellValue("Tổng cộng");
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

            cell = row.createCell(1);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(iSoNguoiChoi);
            
            cell = row.createCell(2);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(iSoUserUnique);
            
            cell = row.createCell(3);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(iTongSoBit);
            
            cell = row.createCell(4);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(dblPTBit);
            
            cell = row.createCell(5);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(dblARPU);
            
            cell = row.createCell(6);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            cell.setCellValue("");
            
            
            sheet.setColumnWidth(0, 256 * 25);
            sheet.setColumnWidth(1, 256 * 25);
            sheet.setColumnWidth(2, 256 * 25);
            sheet.setColumnWidth(3, 256 * 20);
            sheet.setColumnWidth(4, 256 * 20);
            sheet.setColumnWidth(5, 256 * 20);
            sheet.setColumnWidth(6, 256 * 20);
            
            Calendar cal=Calendar.getInstance();
            String fileName = "thongkedoanhthutheogame_" + Utilities.parseDateTime(cal.getTime(), "dd_MM_yyyy") + ".xls";
            File f = new File(getLocalPath() + File.separator + fileName);
            try (FileOutputStream fileOut = FileUtils.openOutputStream(f, true)) {
                wb.write(fileOut);
                fileOut.close();
            }
            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext())
                    .getResourceAsStream(getUrlUploadFiles() + "/" + fileName);
            String contentType = FacesContext.getCurrentInstance().getExternalContext().getMimeType(getLocalPath() + File.separator + fileName);
            return new DefaultStreamedContent(stream, contentType, fileName);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public StreamedContent tongTienCashOutChiTietToExcel(Date tuNgay, Date denNgay, List<RPT_TongCashoutModel> lst) {
       try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Thống kê Cashout");

            Row row;
            Cell cell;

            ConcurrentNavigableMap<NewStyle, CellStyle> styles = initMapStyle(wb);

            Row rowReportTitle = sheet.createRow((short) 0);
            rowReportTitle.setHeightInPoints(30);
            cell = rowReportTitle.createCell(0);
            cell.setCellValue("Thống kê Cashout");
            cell.setCellStyle(styles.get(NewStyle.TitleStyle));
            mergeColspanNoBoder(cell, 3);


            Row rowHeader1 = sheet.createRow((short) 1);
            rowHeader1.setHeightInPoints(20);
            cell = rowHeader1.createCell(0);
            cell.setCellValue(String.format("Từ ngày %s - Đến ngày %s", Utilities.parseDateTime(tuNgay, "dd/MM/yyyy"), Utilities.parseDateTime(denNgay, "dd/MM/yyyy")));
            cell.setCellStyle(styles.get(NewStyle.TitleDate));
            mergeColspanNoBoder(cell, 3);

            row = sheet.createRow(4);

            cell = row.createCell(0);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("CP Name/Tel/Tài khoản");
            mergeRowspan(cell, 1);

            cell = row.createCell(1);
            cell.setCellValue("Số tiền");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

//            cell = row.createCell(2);
//            cell.setCellValue("Hệ Điều Hành");
//            mergeRowspan(cell, 1);
//            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(2);
            cell.setCellValue("Cash type");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(3);
            cell.setCellValue("Thời gian");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));


            
            
            
            int lastRow;
            int rowIndex = 6;
            
         
            
            String strCPID;
            
          
            
            int iTongThanhTien=0;
            int i=0;
            while(i<lst.size())
            {
                RPT_TongCashoutModel item = lst.get(i);
                strCPID=item.getCpid();
                boolean blnFlag_CPID=false;
                
                int iThanhTien_CP=0;
                int iGroup_CPID=rowIndex+1;
                while (i < lst.size() && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))) {
                    if(!blnFlag_CPID)
                    {
                        row=sheet.createRow(rowIndex);
                        lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_CPID, lastRow);
                        sheet.setRowSumsBelow(false);

                        cell = row.createCell(0);
                        cell.setCellValue(lst.get(i).getCpName());
                        //mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        cell = row.createCell(2);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(3);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        rowIndex++;
                    }
                    blnFlag_CPID=true;
                   
                    
                    
                     String strTeleco=lst.get(i).getTelco();
                    boolean blnFlag_Teleco=false;
                    int iGroup_Teleco=rowIndex+1;
                    
                    int iThanhTien_Teleco=0;
                    
                     while (i < lst.size() && (lst.get(i).getTelco()== null ? strTeleco== null : lst.get(i).getTelco().equals(strTeleco))
                             && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))
                             ) {
                         
                            if(!blnFlag_Teleco)
                            {
                                row=sheet.createRow(rowIndex);
                                lastRow = sheet.getLastRowNum();
                                sheet.groupRow(iGroup_Teleco, lastRow);
                                sheet.setRowSumsBelow(false);

                                cell = row.createCell(0);
                                cell.setCellValue(lst.get(i).getTelco());
                                //mergeColspan(cell, 1);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                                
                                cell = row.createCell(1);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                                
                                cell = row.createCell(2);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                                
                                cell = row.createCell(3);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                                rowIndex++;
                            }
                          blnFlag_Teleco=true;
                         
                        if(lst.get(i).getUserName().length()>=4)
                        {
                            lst.get(i).setUserName(lst.get(i).getUserName().substring(0, 4)+"****");
                        }
                           
                         row = sheet.createRow(rowIndex);
                            cell = row.createCell(0);
                            cell.setCellStyle(styles.get(NewStyle.CellText));
                            cell.setCellValue(lst.get(i).getUserName());

                            cell = row.createCell(1);
                            cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                            cell.setCellValue(lst.get(i).getSoTien());

                            cell = row.createCell(2);
                            cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                            cell.setCellValue(lst.get(i).getCashoutType());

                            cell = row.createCell(3);
                            cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                            cell.setCellValue(Utilities.parseDateTime(lst.get(i).getNgay(), "dd/MM/yyyy"));


                             rowIndex++;
                             

                             iThanhTien_CP+=lst.get(i).getSoTien();
                             iThanhTien_Teleco+=lst.get(i).getSoTien();

                              
                             iTongThanhTien+=lst.get(i).getSoTien();

    //                         iTongCP+=lst1.get(i).getSoTK();
    //                         iTongCong+=lst1.get(i).getSoTK();

                             i++;
                       
                       
                        
                     }
                     lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_Teleco, lastRow);
                      
                        sheet.getRow(iGroup_Teleco-1).getCell(1).setCellValue(iThanhTien_Teleco);
                        sheet.getRow(iGroup_Teleco-1).getCell(1).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                }
                 lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_CPID, lastRow);
                     
                      sheet.getRow(iGroup_CPID-1).getCell(1).setCellValue(iThanhTien_CP);
                     sheet.getRow(iGroup_CPID-1).getCell(1).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            }
            
            lastRow = sheet.getLastRowNum();
            lastRow++;
            row = sheet.createRow(lastRow);
            cell = row.createCell(0);
            //mergeColspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            cell.setCellValue("Tổng cộng");
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

            cell = row.createCell(1);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(iTongThanhTien);
            
            cell = row.createCell(2);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            cell.setCellValue(iTongThanhTien);
            
             cell = row.createCell(3);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            cell.setCellValue(iTongThanhTien);
    
            //cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            
            sheet.setColumnWidth(0, 256 * 25);
            sheet.setColumnWidth(1, 256 * 25);
            sheet.setColumnWidth(2, 256 * 25);
            sheet.setColumnWidth(3, 256 * 20);
            
           
            Calendar cal=Calendar.getInstance();
            String fileName = "thongkecashout_" + Utilities.parseDateTime(cal.getTime(), "dd_MM_yyyy") + ".xls";
            File f = new File(getLocalPath() + File.separator + fileName);
            try (FileOutputStream fileOut = FileUtils.openOutputStream(f, true)) {
                wb.write(fileOut);
                fileOut.close();
            }
            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext())
                    .getResourceAsStream(getUrlUploadFiles() + "/" + fileName);
            String contentType = FacesContext.getCurrentInstance().getExternalContext().getMimeType(getLocalPath() + File.separator + fileName);
            return new DefaultStreamedContent(stream, contentType, fileName);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public StreamedContent thongKeDoanhThuTuBitToExcel(Date tuNgay, Date denNgay, List<RPT_TongDoanhThuModel> lst, String type) {
         try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Thống kê doanh thu BIT");

            Row row;
            Cell cell;

            ConcurrentNavigableMap<NewStyle, CellStyle> styles = initMapStyle(wb);

            Row rowReportTitle = sheet.createRow((short) 0);
            rowReportTitle.setHeightInPoints(30);
            cell = rowReportTitle.createCell(0);
            cell.setCellValue("Thống kê doanh thu BIT");
            cell.setCellStyle(styles.get(NewStyle.TitleStyle));
            mergeColspanNoBoder(cell, 5);


            Row rowHeader1 = sheet.createRow((short) 1);
            rowHeader1.setHeightInPoints(20);
            cell = rowHeader1.createCell(0);
            cell.setCellValue(String.format("Từ ngày %s - Đến ngày %s", Utilities.parseDateTime(tuNgay, "dd/MM/yyyy"), Utilities.parseDateTime(denNgay, "dd/MM/yyyy")));
            cell.setCellStyle(styles.get(NewStyle.TitleDate));
            mergeColspanNoBoder(cell, 5);

            row = sheet.createRow(4);

            cell = row.createCell(0);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("CP Name");
            mergeRowspan(cell, 1);
            
            cell = row.createCell(1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("Tên game");
            mergeRowspan(cell, 1);
            
            cell = row.createCell(2);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("Thời gian");
            mergeRowspan(cell, 1);

          
            cell = row.createCell(3);
            cell.setCellValue("Số người chơi");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
            cell = row.createCell(4);
            cell.setCellValue("Tổng số BIT user được");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
            cell = row.createCell(5);
            cell.setCellValue("% commision BIT");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
            int lastRow;
            int rowIndex = 6;
            
         
            
            String strCPID;
            
          
           
            double dblPT=0;
            int  iSoNguoiChoi=0;
            double dblSoBITUserDuoc=0;
            
            int i=0;
            while(i<lst.size())
            {
                RPT_TongDoanhThuModel item = lst.get(i);
                strCPID=item.getCpid();
                boolean blnFlag_CPID=false;
           
                double dblPT_CPID=0;
                int  iSoNguoiChoi_CPID=0;
                double dblSoBITUserDuoc_CPID=0;
                
                int iGroup_CPID=rowIndex+1;
                while (i < lst.size() && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))) {
                    if(!blnFlag_CPID)
                    {
                        row=sheet.createRow(rowIndex);
                        lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_CPID, lastRow);
                        sheet.setRowSumsBelow(false);

                        cell = row.createCell(0);
                        cell.setCellValue(lst.get(i).getCpName());
                       
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(1);
                        cell.setCellValue(lst.get(i).getGame());
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(2);
                        cell.setCellValue("");
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(3);
                        cell.setCellValue("");
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        cell = row.createCell(4);
                        cell.setCellValue("");
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        cell = row.createCell(5);
                        cell.setCellValue("");
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        rowIndex++;
                    }
                    blnFlag_CPID=true;
                   
                    row = sheet.createRow(rowIndex);
                    
                    cell = row.createCell(0);
                    cell.setCellStyle(styles.get(NewStyle.CellText));
                    cell.setCellValue("");
                    
                    cell = row.createCell(1);
                    cell.setCellStyle(styles.get(NewStyle.CellText));
                    cell.setCellValue(lst.get(i).getGame());
                    
                    cell = row.createCell(2);
                    cell.setCellStyle(styles.get(NewStyle.CellText));
                    cell.setCellValue(Utilities.parseDateTime(lst.get(i).getNgay(), "dd/MM/yyyy"));

                    cell = row.createCell(3);
                    cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                    cell.setCellValue(lst.get(i).getSoNguoiChoi());
                    
                     cell = row.createCell(4);
                    cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                    cell.setCellValue(lst.get(i).getTongSoBit());
                    
                    cell = row.createCell(5);
                    cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                    cell.setCellValue(lst.get(i).getPtBit());

                    if(Sys_NguoiDungController.findUser().isIsAdmin())
                    {
                        cell.setCellValue(Math.round(lst.get(i).getTongSoBit()*2/100));
               
                        dblPT_CPID+=lst.get(i).getTongSoBit()*2/100;
                          dblPT+=lst.get(i).getTongSoBit()*2/100;
                    }
                    else
                    {
                        cell.setCellValue(Math.round(lst.get(i).getTongSoBit()*2/100));
               
                        dblPT_CPID+=(lst.get(i).getTongSoBit()*2/100)*30/100;
                        dblPT+=(lst.get(i).getTongSoBit()*2/100)*30/100;
                      
                    }
                   
                    iSoNguoiChoi_CPID+=lst.get(i).getSoNguoiChoi();
                    dblSoBITUserDuoc_CPID+=lst.get(i).getTongSoBit();
                        
                    iSoNguoiChoi+=lst.get(i).getSoNguoiChoi();
                    dblSoBITUserDuoc+=lst.get(i).getTongSoBit();
                    
                    
                     rowIndex++;

                     i++;
                             
                }
                 lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_CPID, lastRow);
                     
                     sheet.getRow(iGroup_CPID-1).getCell(3).setCellValue(iSoNguoiChoi_CPID);
                     sheet.getRow(iGroup_CPID-1).getCell(3).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                     sheet.getRow(iGroup_CPID-1).getCell(4).setCellValue(dblSoBITUserDuoc_CPID);
                     sheet.getRow(iGroup_CPID-1).getCell(4).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                     sheet.getRow(iGroup_CPID-1).getCell(5).setCellValue(Math.round(dblPT_CPID));
                     sheet.getRow(iGroup_CPID-1).getCell(5).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                     //dblARPU+=Math.round(dblARPU_CPID/(lTongSoLuong<=0?1:lTongSoLuong));
                  
            }
            
            lastRow = sheet.getLastRowNum();
            lastRow++;
            row = sheet.createRow(lastRow);
            
            cell = row.createCell(0);
            mergeColspan(cell, 2);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            cell.setCellValue("Tổng cộng");
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

            cell = row.createCell(3);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(iSoNguoiChoi);
            
             cell = row.createCell(4);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(dblSoBITUserDuoc);
            
            cell = row.createCell(5);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(Math.round(dblPT));
            
            sheet.setColumnWidth(0, 256 * 25);
            sheet.setColumnWidth(1, 256 * 25);
            sheet.setColumnWidth(2, 256 * 25);
            sheet.setColumnWidth(3, 256 * 25);
            sheet.setColumnWidth(4, 256 * 25);
            sheet.setColumnWidth(5, 256 * 25);
            
            
            Calendar cal=Calendar.getInstance();
            String fileName = "thongkedoanhthu_BIT_" + Utilities.parseDateTime(cal.getTime(), "dd_MM_yyyy") + ".xls";
            File f = new File(getLocalPath() + File.separator + fileName);
            try (FileOutputStream fileOut = FileUtils.openOutputStream(f, true)) {
                wb.write(fileOut);
                fileOut.close();
            }
            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext())
                    .getResourceAsStream(getUrlUploadFiles() + "/" + fileName);
            String contentType = FacesContext.getCurrentInstance().getExternalContext().getMimeType(getLocalPath() + File.separator + fileName);
            return new DefaultStreamedContent(stream, contentType, fileName);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    public StreamedContent thongKeDoanhThuTuBacToExcel(Date tuNgay, Date denNgay, List<RPT_TongDoanhThuModel> lst, String type) {
         try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Thống kê doanh thu BẠC");

            Row row;
            Cell cell;

            ConcurrentNavigableMap<NewStyle, CellStyle> styles = initMapStyle(wb);

            Row rowReportTitle = sheet.createRow((short) 0);
            rowReportTitle.setHeightInPoints(30);
            cell = rowReportTitle.createCell(0);
            cell.setCellValue("Thống kê doanh thu BẠC");
            cell.setCellStyle(styles.get(NewStyle.TitleStyle));
            mergeColspanNoBoder(cell, 5);


            Row rowHeader1 = sheet.createRow((short) 1);
            rowHeader1.setHeightInPoints(20);
            cell = rowHeader1.createCell(0);
            cell.setCellValue(String.format("Từ ngày %s - Đến ngày %s", Utilities.parseDateTime(tuNgay, "dd/MM/yyyy"), Utilities.parseDateTime(denNgay, "dd/MM/yyyy")));
            cell.setCellStyle(styles.get(NewStyle.TitleDate));
            mergeColspanNoBoder(cell, 5);

            row = sheet.createRow(4);

            cell = row.createCell(0);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("CP Name");
            mergeRowspan(cell, 1);
            
            cell = row.createCell(1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("Tên game");
            mergeRowspan(cell, 1);
            
            cell = row.createCell(2);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("Thời gian");
            mergeRowspan(cell, 1);

          
            cell = row.createCell(3);
            cell.setCellValue("Số tài khoản đổi");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
            cell = row.createCell(4);
            cell.setCellValue("Tổng số BẠC đổi");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
            cell = row.createCell(5);
            cell.setCellValue("% cho CP");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
            int lastRow;
            int rowIndex = 6;
            
         
            
            String strCPID;
            
          
           
            double dblPT=0;
            int  iSoNguoiChoi=0;
            double dblSoBITUserDuoc=0;
            
            int i=0;
            while(i<lst.size())
            {
                RPT_TongDoanhThuModel item = lst.get(i);
                strCPID=item.getCpid();
                boolean blnFlag_CPID=false;
           
                double dblPT_CPID=0;
                int  iSoNguoiChoi_CPID=0;
                double dblSoBITUserDuoc_CPID=0;
                
                int iGroup_CPID=rowIndex+1;
                while (i < lst.size() && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))) {
                    if(!blnFlag_CPID)
                    {
                        row=sheet.createRow(rowIndex);
                        lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_CPID, lastRow);
                        sheet.setRowSumsBelow(false);

                        cell = row.createCell(0);
                        cell.setCellValue(lst.get(i).getCpName());
                       
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(1);
                        cell.setCellValue(lst.get(i).getGame());
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(2);
                        cell.setCellValue("");
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(3);
                        cell.setCellValue("");
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        cell = row.createCell(4);
                        cell.setCellValue("");
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        cell = row.createCell(5);
                        cell.setCellValue("");
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        rowIndex++;
                    }
                    blnFlag_CPID=true;
                   
                     lst.get(i).setPT((float)lst.get(i).getTongSoBit() * 20 / 100);
                    
                    row = sheet.createRow(rowIndex);
                    
                    
                    cell = row.createCell(0);
                    cell.setCellStyle(styles.get(NewStyle.CellText));
                    cell.setCellValue("");
                    
                    cell = row.createCell(1);
                    cell.setCellStyle(styles.get(NewStyle.CellText));
                    cell.setCellValue(lst.get(i).getGame());
                    
                    cell = row.createCell(2);
                    cell.setCellStyle(styles.get(NewStyle.CellText));
                    cell.setCellValue(Utilities.parseDateTime(lst.get(i).getNgay(), "dd/MM/yyyy"));

                    cell = row.createCell(3);
                    cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                    cell.setCellValue(lst.get(i).getSoNguoiChoi());
                    
                     cell = row.createCell(4);
                    cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                    cell.setCellValue(lst.get(i).getTongSoBit());
                    
                    cell = row.createCell(5);
                    cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                    cell.setCellValue(lst.get(i).getPtBit());

                    cell.setCellValue(Math.round(lst.get(i).getPT()));
               
                    dblPT_CPID+=lst.get(i).getPT();
                      dblPT+=lst.get(i).getPT();
                   
                    iSoNguoiChoi_CPID+=lst.get(i).getSoNguoiChoi();
                    dblSoBITUserDuoc_CPID+=lst.get(i).getTongSoBit();
                        
                    iSoNguoiChoi+=lst.get(i).getSoNguoiChoi();
                    dblSoBITUserDuoc+=lst.get(i).getTongSoBit();
                    
                    
                     rowIndex++;

                     i++;
                             
                }
                 lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_CPID, lastRow);
                     
                     sheet.getRow(iGroup_CPID-1).getCell(3).setCellValue(iSoNguoiChoi_CPID);
                     sheet.getRow(iGroup_CPID-1).getCell(3).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                     sheet.getRow(iGroup_CPID-1).getCell(4).setCellValue(dblSoBITUserDuoc_CPID);
                     sheet.getRow(iGroup_CPID-1).getCell(4).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                     sheet.getRow(iGroup_CPID-1).getCell(5).setCellValue(dblPT_CPID);
                     sheet.getRow(iGroup_CPID-1).getCell(5).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                     //dblARPU+=Math.round(dblARPU_CPID/(lTongSoLuong<=0?1:lTongSoLuong));
                  
            }
            
            lastRow = sheet.getLastRowNum();
            lastRow++;
            row = sheet.createRow(lastRow);
            
            cell = row.createCell(0);
            mergeColspan(cell, 2);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            cell.setCellValue("Tổng cộng");
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

            cell = row.createCell(3);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(iSoNguoiChoi);
            
             cell = row.createCell(4);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(dblSoBITUserDuoc);
            
            cell = row.createCell(5);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(dblPT);
            
            sheet.setColumnWidth(0, 256 * 25);
            sheet.setColumnWidth(1, 256 * 25);
            sheet.setColumnWidth(2, 256 * 25);
            sheet.setColumnWidth(3, 256 * 25);
            sheet.setColumnWidth(4, 256 * 25);
            sheet.setColumnWidth(5, 256 * 25);
            
            
            Calendar cal=Calendar.getInstance();
            String fileName = "thongkedoanhthu_BAC_" + Utilities.parseDateTime(cal.getTime(), "dd_MM_yyyy") + ".xls";
            File f = new File(getLocalPath() + File.separator + fileName);
            try (FileOutputStream fileOut = FileUtils.openOutputStream(f, true)) {
                wb.write(fileOut);
                fileOut.close();
            }
            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext())
                    .getResourceAsStream(getUrlUploadFiles() + "/" + fileName);
            String contentType = FacesContext.getCurrentInstance().getExternalContext().getMimeType(getLocalPath() + File.separator + fileName);
            return new DefaultStreamedContent(stream, contentType, fileName);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    public StreamedContent thongKeDoanhThuToExcel(Date tuNgay, Date denNgay, List<RPT_TongDoanhThuModel> lst, String type) {
         try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Thống kê doanh");

            Row row;
            Cell cell;

            ConcurrentNavigableMap<NewStyle, CellStyle> styles = initMapStyle(wb);

            Row rowReportTitle = sheet.createRow((short) 0);
            rowReportTitle.setHeightInPoints(30);
            cell = rowReportTitle.createCell(0);
            cell.setCellValue("Thống kê doanh");
            cell.setCellStyle(styles.get(NewStyle.TitleStyle));
            mergeColspanNoBoder(cell, 5);


            Row rowHeader1 = sheet.createRow((short) 1);
            rowHeader1.setHeightInPoints(20);
            cell = rowHeader1.createCell(0);
            cell.setCellValue(String.format("Từ ngày %s - Đến ngày %s", Utilities.parseDateTime(tuNgay, "dd/MM/yyyy"), Utilities.parseDateTime(denNgay, "dd/MM/yyyy")));
            cell.setCellStyle(styles.get(NewStyle.TitleDate));
            mergeColspanNoBoder(cell, 5);

            row = sheet.createRow(4);

            cell = row.createCell(0);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("CP Name");
            mergeRowspan(cell, 1);
            
            cell = row.createCell(1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("Tên game");
            mergeRowspan(cell, 1);
            
            cell = row.createCell(2);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("Thời gian");
            mergeRowspan(cell, 1);

          
            cell = row.createCell(3);
            cell.setCellValue("% Commision BIT");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
            cell = row.createCell(4);
            cell.setCellValue("Tổng số BẠC đổi");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
            cell = row.createCell(5);
            cell.setCellValue("Tổng doanh thu");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
            int lastRow;
            int rowIndex = 6;
            
         
            
            String strCPID;
            
          
           
            double dblPT=0;
           
            double dblSoBITUserDuoc=0;
            
            int i=0;
            while(i<lst.size())
            {
                RPT_TongDoanhThuModel item = lst.get(i);
                strCPID=item.getCpid();
                boolean blnFlag_CPID=false;
           
                double dblPT_CPID=0;
               
                double dblSoBITUserDuoc_CPID=0;
                
                int iGroup_CPID=rowIndex+1;
                while (i < lst.size() && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))) {
                    if(!blnFlag_CPID)
                    {
                        row=sheet.createRow(rowIndex);
                        lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_CPID, lastRow);
                        sheet.setRowSumsBelow(false);

                        cell = row.createCell(0);
                        cell.setCellValue(lst.get(i).getCpName());
                       
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(1);
                        cell.setCellValue(lst.get(i).getGame());
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(2);
                        cell.setCellValue("");
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(3);
                        cell.setCellValue("");
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        cell = row.createCell(4);
                        cell.setCellValue("");
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        cell = row.createCell(5);
                        cell.setCellValue("");
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        rowIndex++;
                    }
                    blnFlag_CPID=true;
                   
                    // lst.get(i).setPT((float)lst.get(i).getTongSoBit() * 20 / 100);
                    
                    row = sheet.createRow(rowIndex);
                    
                    
                    cell = row.createCell(0);
                    cell.setCellStyle(styles.get(NewStyle.CellText));
                    cell.setCellValue("");
                    
                    cell = row.createCell(1);
                    cell.setCellStyle(styles.get(NewStyle.CellText));
                    cell.setCellValue(lst.get(i).getGame());
                    
                    cell = row.createCell(2);
                    cell.setCellStyle(styles.get(NewStyle.CellText));
                    cell.setCellValue(Utilities.parseDateTime(lst.get(i).getNgay(), "dd/MM/yyyy"));
                    
                    double dblPTBit;
                    if(Sys_NguoiDungController.findUser().isIsAdmin())
                    {
                        dblPTBit=Math.round(lst.get(i).getPtBit());
                    }
                    else
                        dblPTBit=Math.round(lst.get(i).getPtBit()*30/100);
                    
                    
                    cell = row.createCell(3);
                    cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                    cell.setCellValue(dblPTBit);
                    
                     cell = row.createCell(4);
                    cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                    cell.setCellValue(lst.get(i).getTongSoBit());
                    
                    cell = row.createCell(5);
                    cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                    cell.setCellValue(dblPTBit+lst.get(i).getTongSoBit());

                    //cell.setCellValue(Math.round(lst.get(i).getPT()));
               
                    dblPT_CPID+=dblPTBit;
                    dblPT+=dblPTBit;
                   
                    
                    dblSoBITUserDuoc_CPID+=lst.get(i).getTongSoBit();
                        
                   
                    dblSoBITUserDuoc+=lst.get(i).getTongSoBit();
                    
                    
                     rowIndex++;

                     i++;
                             
                }
                 lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_CPID, lastRow);
                     
                     sheet.getRow(iGroup_CPID-1).getCell(3).setCellValue(Math.round(dblPT_CPID));
                     sheet.getRow(iGroup_CPID-1).getCell(3).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                     sheet.getRow(iGroup_CPID-1).getCell(4).setCellValue(dblSoBITUserDuoc_CPID);
                     sheet.getRow(iGroup_CPID-1).getCell(4).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                     sheet.getRow(iGroup_CPID-1).getCell(5).setCellValue(Math.round(dblPT_CPID)+dblSoBITUserDuoc_CPID);
                     sheet.getRow(iGroup_CPID-1).getCell(5).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                     //dblARPU+=Math.round(dblARPU_CPID/(lTongSoLuong<=0?1:lTongSoLuong));
                  
            }
            
            lastRow = sheet.getLastRowNum();
            lastRow++;
            row = sheet.createRow(lastRow);
            
            cell = row.createCell(0);
            mergeColspan(cell, 2);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            cell.setCellValue("Tổng cộng");
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

            cell = row.createCell(3);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(Math.round(dblPT));
            
             cell = row.createCell(4);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(dblSoBITUserDuoc);
            
            cell = row.createCell(5);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(Math.round(dblPT)+dblSoBITUserDuoc);
            
            sheet.setColumnWidth(0, 256 * 25);
            sheet.setColumnWidth(1, 256 * 25);
            sheet.setColumnWidth(2, 256 * 25);
            sheet.setColumnWidth(3, 256 * 25);
            sheet.setColumnWidth(4, 256 * 25);
            sheet.setColumnWidth(5, 256 * 25);
            
            
            Calendar cal=Calendar.getInstance();
            String fileName = "thongkedoanhthu_" + Utilities.parseDateTime(cal.getTime(), "dd_MM_yyyy") + ".xls";
            File f = new File(getLocalPath() + File.separator + fileName);
            try (FileOutputStream fileOut = FileUtils.openOutputStream(f, true)) {
                wb.write(fileOut);
                fileOut.close();
            }
            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext())
                    .getResourceAsStream(getUrlUploadFiles() + "/" + fileName);
            String contentType = FacesContext.getCurrentInstance().getExternalContext().getMimeType(getLocalPath() + File.separator + fileName);
            return new DefaultStreamedContent(stream, contentType, fileName);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    
    public StreamedContent thongKeKhacToExcel(Date tuNgay, Date denNgay, List<RPT_ThongKeKhacModel> lst) {
       try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Thống Kê DAU");

            Row row;
            Cell cell;

            ConcurrentNavigableMap<NewStyle, CellStyle> styles = initMapStyle(wb);

            Row rowReportTitle = sheet.createRow((short) 0);
            rowReportTitle.setHeightInPoints(30);
            cell = rowReportTitle.createCell(0);
            cell.setCellValue("Thống Kê DAU");
            cell.setCellStyle(styles.get(NewStyle.TitleStyle));
            mergeColspanNoBoder(cell, 5);


            Row rowHeader1 = sheet.createRow((short) 1);
            rowHeader1.setHeightInPoints(20);
            cell = rowHeader1.createCell(0);
            cell.setCellValue(String.format("Từ ngày %s - Đến ngày %s", Utilities.parseDateTime(tuNgay, "dd/MM/yyyy"), Utilities.parseDateTime(denNgay, "dd/MM/yyyy")));
            cell.setCellStyle(styles.get(NewStyle.TitleDate));
            mergeColspanNoBoder(cell, 5);

            row = sheet.createRow(4);

            cell = row.createCell(0);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("CP Name");
            mergeRowspan(cell, 1);

            cell = row.createCell(1);
            cell.setCellValue("Thời gian");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));


            cell = row.createCell(2);
            cell.setCellValue("Tên game");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(3);
            cell.setCellValue("Số lượng");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
           
            
            int lastRow;
            int rowIndex = 6;
            
         
            
            String strCPID;
            
          
             int iSoNguoiChoi=0;
           
                
            int i=0;
            while(i<lst.size())
            {
                RPT_ThongKeKhacModel item = lst.get(i);
                strCPID=item.getCpid();
                boolean blnFlag_CPID=false;
                int iSoNguoiChoi_CPID=0;
               
                int iGroup_CPID=rowIndex+1;
                while (i < lst.size() && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))) {
                    if(!blnFlag_CPID)
                    {
                        row=sheet.createRow(rowIndex);
                        lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_CPID, lastRow);
                        sheet.setRowSumsBelow(false);

                        cell = row.createCell(0);
                        cell.setCellValue(lst.get(i).getCpName());
                        mergeColspan(cell, 2);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(3);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                      
                        
                        rowIndex++;
                    }
                    blnFlag_CPID=true;
                   
                    
                    
                  
                     row = sheet.createRow(rowIndex);
                        cell = row.createCell(0);
                        cell.setCellStyle(styles.get(NewStyle.CellText));
                        cell.setCellValue("");
                        
                        cell = row.createCell(1);
                        cell.setCellStyle(styles.get(NewStyle.CellText));
                        cell.setCellValue(Utilities.parseDateTime(lst.get(i).getNgay(), "dd/MM/yyyy"));
                        
                        cell = row.createCell(2);
                        cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                        cell.setCellValue(lst.get(i).getGame());
                        
                         cell = row.createCell(3);
                        cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                        cell.setCellValue(lst.get(i).getSoLuong());
                        
                        
                         rowIndex++;
                         
                         iSoNguoiChoi+=lst.get(i).getSoLuong();
                      
                         iSoNguoiChoi_CPID+=lst.get(i).getSoLuong();
                        
                         i++;
                     
                }
                 lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_CPID, lastRow);
                     
                     sheet.getRow(iGroup_CPID-1).getCell(3).setCellValue(iSoNguoiChoi_CPID);
                     sheet.getRow(iGroup_CPID-1).getCell(3).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     
                 
            }
            
            lastRow = sheet.getLastRowNum();
            lastRow++;
            row = sheet.createRow(lastRow);
            
            cell = row.createCell(0);
           mergeColspan(cell, 2);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            cell.setCellValue("Tổng cộng");
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

            cell = row.createCell(3);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(iSoNguoiChoi);
            
          
            
            
            sheet.setColumnWidth(0, 256 * 25);
            sheet.setColumnWidth(1, 256 * 25);
            sheet.setColumnWidth(2, 256 * 25);
            sheet.setColumnWidth(3, 256 * 20);
           
            
            Calendar cal=Calendar.getInstance();
            String fileName = "thongkekhac_DAU_" + Utilities.parseDateTime(cal.getTime(), "dd_MM_yyyy") + ".xls";
            File f = new File(getLocalPath() + File.separator + fileName);
            try (FileOutputStream fileOut = FileUtils.openOutputStream(f, true)) {
                wb.write(fileOut);
                fileOut.close();
            }
            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext())
                    .getResourceAsStream(getUrlUploadFiles() + "/" + fileName);
            String contentType = FacesContext.getCurrentInstance().getExternalContext().getMimeType(getLocalPath() + File.separator + fileName);
            return new DefaultStreamedContent(stream, contentType, fileName);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    public StreamedContent thongKeKhacToExcel_ARPU(Date tuNgay, Date denNgay, List<RPT_ThongKeKhacModel> lst) {
         try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Thống kê ARPU");

            Row row;
            Cell cell;

            ConcurrentNavigableMap<NewStyle, CellStyle> styles = initMapStyle(wb);

            Row rowReportTitle = sheet.createRow((short) 0);
            rowReportTitle.setHeightInPoints(30);
            cell = rowReportTitle.createCell(0);
            cell.setCellValue("Thống kê ARPU");
            cell.setCellStyle(styles.get(NewStyle.TitleStyle));
            mergeColspanNoBoder(cell, 2);


            Row rowHeader1 = sheet.createRow((short) 1);
            rowHeader1.setHeightInPoints(20);
            cell = rowHeader1.createCell(0);
            cell.setCellValue(String.format("Từ ngày %s - Đến ngày %s", Utilities.parseDateTime(tuNgay, "dd/MM/yyyy"), Utilities.parseDateTime(denNgay, "dd/MM/yyyy")));
            cell.setCellStyle(styles.get(NewStyle.TitleDate));
            mergeColspanNoBoder(cell, 2);

            row = sheet.createRow(4);

            cell = row.createCell(0);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("CP Name");
            mergeRowspan(cell, 1);

            cell = row.createCell(1);
            cell.setCellValue("Tên game");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));

            cell = row.createCell(2);
            cell.setCellValue("ARPU");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
         
            
            
            
            int lastRow;
            int rowIndex = 6;
            
         
            
            String strCPID;
            
          
           
            double dblARPU=0;
            long lTongSoLuong=0;
            int i=0;
            while(i<lst.size())
            {
                RPT_ThongKeKhacModel item = lst.get(i);
                strCPID=item.getCpid();
                boolean blnFlag_CPID=false;
           
                double dblARPU_CPID=0;
                
                int iGroup_CPID=rowIndex+1;
                while (i < lst.size() && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))) {
                    if(!blnFlag_CPID)
                    {
                        row=sheet.createRow(rowIndex);
                        lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_CPID, lastRow);
                        sheet.setRowSumsBelow(false);

                        cell = row.createCell(0);
                        cell.setCellValue(lst.get(i).getCpName());
                        mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(2);
                        cell.setCellValue("");
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        rowIndex++;
                    }
                    blnFlag_CPID=true;
                   
                    String strThang=lst.get(i).getThang();
                    boolean blnFlag_Thang=false;
                    int iGroup_Thang=rowIndex+1;
                    
                    double ARPU_Thang=0;
                    long lTongSoUserTheoThang=0;
                    
                     while (i < lst.size() && (lst.get(i).getThang()== null ? strThang== null : lst.get(i).getThang().equals(strThang))
                             && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))
                             ) {
                         
                            if(!blnFlag_Thang)
                            {
                                row=sheet.createRow(rowIndex);
                                lastRow = sheet.getLastRowNum();
                                sheet.groupRow(iGroup_Thang, lastRow);
                                sheet.setRowSumsBelow(false);

                                cell = row.createCell(0);
                                cell.setCellValue(lst.get(i).getThang());
                                mergeColspan(cell, 1);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                                
                                cell = row.createCell(2);
                                cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                                
                                rowIndex++;
                            }
                          blnFlag_Thang=true;
                         
                      
                           
                            row = sheet.createRow(rowIndex);
                            cell = row.createCell(0);
                            cell.setCellStyle(styles.get(NewStyle.CellText));
                            cell.setCellValue(Utilities.parseDateTime(lst.get(i).getNgay(), "dd/MM/yyyy"));

                            cell = row.createCell(1);
                            cell.setCellStyle(styles.get(NewStyle.CellText));
                            cell.setCellValue(lst.get(i).getGame());

                            cell = row.createCell(2);
                            cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                            cell.setCellValue(lst.get(i).getSoTien()/lst.get(i).getSoLuong());

                            lTongSoUserTheoThang=lst.get(i).getTongSoLuongThang();
                            lTongSoLuong=lst.get(i).getTongCong();

                             rowIndex++;
                             

                             //dblARPU+=lst.get(i).getSoTien();
                             dblARPU_CPID+=lst.get(i).getSoTien();
                             ARPU_Thang+=lst.get(i).getSoTien();
                              
                             
                             i++;
                       
                       
                        
                     }
                     lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_Thang, lastRow);
                      
                        sheet.getRow(iGroup_Thang-1).getCell(2).setCellValue(Math.round(ARPU_Thang/lTongSoUserTheoThang));
                        sheet.getRow(iGroup_Thang-1).getCell(2).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                }
                 lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_CPID, lastRow);
                     
                     sheet.getRow(iGroup_CPID-1).getCell(2).setCellValue(Math.round(dblARPU_CPID/(lTongSoLuong<=0?1:lTongSoLuong)));
                     sheet.getRow(iGroup_CPID-1).getCell(2).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     dblARPU+=Math.round(dblARPU_CPID/(lTongSoLuong<=0?1:lTongSoLuong));
                  
            }
            
            lastRow = sheet.getLastRowNum();
            lastRow++;
            row = sheet.createRow(lastRow);
            
            cell = row.createCell(0);
           mergeColspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            cell.setCellValue("Tổng cộng");
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

            cell = row.createCell(2);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(dblARPU);
            
            
            
            sheet.setColumnWidth(0, 256 * 25);
            sheet.setColumnWidth(1, 256 * 25);
            sheet.setColumnWidth(2, 256 * 25);
            sheet.setColumnWidth(3, 256 * 20);
            
            
            Calendar cal=Calendar.getInstance();
            String fileName = "thongkekhac_ARPU_" + Utilities.parseDateTime(cal.getTime(), "dd_MM_yyyy") + ".xls";
            File f = new File(getLocalPath() + File.separator + fileName);
            try (FileOutputStream fileOut = FileUtils.openOutputStream(f, true)) {
                wb.write(fileOut);
                fileOut.close();
            }
            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext())
                    .getResourceAsStream(getUrlUploadFiles() + "/" + fileName);
            String contentType = FacesContext.getCurrentInstance().getExternalContext().getMimeType(getLocalPath() + File.separator + fileName);
            return new DefaultStreamedContent(stream, contentType, fileName);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    public StreamedContent thongKeKhacToExcel_BIT(Date tuNgay, Date denNgay, List<RPT_ThongKeKhacModel> lst) {
         try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Thống kê BIT");

            Row row;
            Cell cell;

            ConcurrentNavigableMap<NewStyle, CellStyle> styles = initMapStyle(wb);

            Row rowReportTitle = sheet.createRow((short) 0);
            rowReportTitle.setHeightInPoints(30);
            cell = rowReportTitle.createCell(0);
            cell.setCellValue("Thống kê BIT");
            cell.setCellStyle(styles.get(NewStyle.TitleStyle));
            mergeColspanNoBoder(cell, 1);


            Row rowHeader1 = sheet.createRow((short) 1);
            rowHeader1.setHeightInPoints(20);
            cell = rowHeader1.createCell(0);
            cell.setCellValue(String.format("Từ ngày %s - Đến ngày %s", Utilities.parseDateTime(tuNgay, "dd/MM/yyyy"), Utilities.parseDateTime(denNgay, "dd/MM/yyyy")));
            cell.setCellStyle(styles.get(NewStyle.TitleDate));
            mergeColspanNoBoder(cell, 1);

            row = sheet.createRow(4);

            cell = row.createCell(0);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("CP Name");
            mergeRowspan(cell, 1);

          
            cell = row.createCell(1);
            cell.setCellValue("Số lượng");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
         
            
            
            
            int lastRow;
            int rowIndex = 6;
            
         
            
            String strCPID;
            
          
           
            double dblARPU=0;
            long lTongSoLuong=0;
            int i=0;
            while(i<lst.size())
            {
                RPT_ThongKeKhacModel item = lst.get(i);
                strCPID=item.getCpid();
                boolean blnFlag_CPID=false;
           
                long lTongSoLuong_CPID=0;
                
                int iGroup_CPID=rowIndex+1;
                while (i < lst.size() && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))) {
                    if(!blnFlag_CPID)
                    {
                        row=sheet.createRow(rowIndex);
                        lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_CPID, lastRow);
                        sheet.setRowSumsBelow(false);

                        cell = row.createCell(0);
                        cell.setCellValue(lst.get(i).getCpName());
                       
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(1);
                        cell.setCellValue("");
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        rowIndex++;
                    }
                    blnFlag_CPID=true;
                   
                    row = sheet.createRow(rowIndex);
                    cell = row.createCell(0);
                    cell.setCellStyle(styles.get(NewStyle.CellText));
                    cell.setCellValue(Utilities.parseDateTime(lst.get(i).getNgay(), "dd/MM/yyyy"));

                    cell = row.createCell(1);
                    cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                    cell.setCellValue(lst.get(i).getSoTien());

                    lTongSoLuong=lst.get(i).getSoTien();
                    lTongSoLuong_CPID=lst.get(i).getSoTien();
                     rowIndex++;

                     i++;
                             
                }
                 lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_CPID, lastRow);
                     
                     sheet.getRow(iGroup_CPID-1).getCell(1).setCellValue(lTongSoLuong_CPID);
                     sheet.getRow(iGroup_CPID-1).getCell(1).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     //dblARPU+=Math.round(dblARPU_CPID/(lTongSoLuong<=0?1:lTongSoLuong));
                  
            }
            
            lastRow = sheet.getLastRowNum();
            lastRow++;
            row = sheet.createRow(lastRow);
            
            cell = row.createCell(0);
          // mergeColspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            cell.setCellValue("Tổng cộng");
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

            cell = row.createCell(1);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(lTongSoLuong);
            
            
            
            sheet.setColumnWidth(0, 256 * 25);
            sheet.setColumnWidth(1, 256 * 25);

            
            
            Calendar cal=Calendar.getInstance();
            String fileName = "thongkekhac_BIT_" + Utilities.parseDateTime(cal.getTime(), "dd_MM_yyyy") + ".xls";
            File f = new File(getLocalPath() + File.separator + fileName);
            try (FileOutputStream fileOut = FileUtils.openOutputStream(f, true)) {
                wb.write(fileOut);
                fileOut.close();
            }
            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext())
                    .getResourceAsStream(getUrlUploadFiles() + "/" + fileName);
            String contentType = FacesContext.getCurrentInstance().getExternalContext().getMimeType(getLocalPath() + File.separator + fileName);
            return new DefaultStreamedContent(stream, contentType, fileName);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    public StreamedContent thongKeKhacToExcel_BAC(Date tuNgay, Date denNgay, List<RPT_ThongKeKhacModel> lst) {
         try {
            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Thống kê BẠC");

            Row row;
            Cell cell;

            ConcurrentNavigableMap<NewStyle, CellStyle> styles = initMapStyle(wb);

            Row rowReportTitle = sheet.createRow((short) 0);
            rowReportTitle.setHeightInPoints(30);
            cell = rowReportTitle.createCell(0);
            cell.setCellValue("Thống kê BẠC");
            cell.setCellStyle(styles.get(NewStyle.TitleStyle));
            mergeColspanNoBoder(cell, 1);


            Row rowHeader1 = sheet.createRow((short) 1);
            rowHeader1.setHeightInPoints(20);
            cell = rowHeader1.createCell(0);
            cell.setCellValue(String.format("Từ ngày %s - Đến ngày %s", Utilities.parseDateTime(tuNgay, "dd/MM/yyyy"), Utilities.parseDateTime(denNgay, "dd/MM/yyyy")));
            cell.setCellStyle(styles.get(NewStyle.TitleDate));
            mergeColspanNoBoder(cell, 1);

            row = sheet.createRow(4);

            cell = row.createCell(0);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            cell.setCellValue("CP Name");
            mergeRowspan(cell, 1);

          
            cell = row.createCell(1);
            cell.setCellValue("Số lượng");
            mergeRowspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.HeaderStyle));
            
         
            
            
            
            int lastRow;
            int rowIndex = 6;
            
         
            
            String strCPID;
            
          
           
            double dblARPU=0;
            long lTongSoLuong=0;
            int i=0;
            while(i<lst.size())
            {
                RPT_ThongKeKhacModel item = lst.get(i);
                strCPID=item.getCpid();
                boolean blnFlag_CPID=false;
           
                long lTongSoLuong_CPID=0;
                
                int iGroup_CPID=rowIndex+1;
                while (i < lst.size() && (lst.get(i).getCpid() == null ? strCPID == null : lst.get(i).getCpid().equals(strCPID))) {
                    if(!blnFlag_CPID)
                    {
                        row=sheet.createRow(rowIndex);
                        lastRow = sheet.getLastRowNum();
                        sheet.groupRow(iGroup_CPID, lastRow);
                        sheet.setRowSumsBelow(false);

                        cell = row.createCell(0);
                        cell.setCellValue(lst.get(i).getCpName());
                       
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
                        
                        cell = row.createCell(1);
                        cell.setCellValue("");
                       // mergeColspan(cell, 1);
                        cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                        
                        rowIndex++;
                    }
                    blnFlag_CPID=true;
                   
                    row = sheet.createRow(rowIndex);
                    cell = row.createCell(0);
                    cell.setCellStyle(styles.get(NewStyle.CellText));
                    cell.setCellValue(Utilities.parseDateTime(lst.get(i).getNgay(), "dd/MM/yyyy"));

                    cell = row.createCell(1);
                    cell.setCellStyle(styles.get(NewStyle.CellNumberic));
                    cell.setCellValue(lst.get(i).getSoTien());

                    lTongSoLuong=lst.get(i).getSoTien();
                    lTongSoLuong_CPID=lst.get(i).getSoTien();
                     rowIndex++;

                     i++;
                             
                }
                 lastRow = sheet.getLastRowNum();
                     sheet.groupRow(iGroup_CPID, lastRow);
                     
                     sheet.getRow(iGroup_CPID-1).getCell(1).setCellValue(lTongSoLuong_CPID);
                     sheet.getRow(iGroup_CPID-1).getCell(1).setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
                     //dblARPU+=Math.round(dblARPU_CPID/(lTongSoLuong<=0?1:lTongSoLuong));
                  
            }
            
            lastRow = sheet.getLastRowNum();
            lastRow++;
            row = sheet.createRow(lastRow);
            
            cell = row.createCell(0);
          // mergeColspan(cell, 1);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));
            cell.setCellValue("Tổng cộng");
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryText));

            cell = row.createCell(1);
            cell.setCellStyle(styles.get(NewStyle.GroupSummaryNumberic));
            cell.setCellValue(lTongSoLuong);
            
            
            
            sheet.setColumnWidth(0, 256 * 25);
            sheet.setColumnWidth(1, 256 * 25);

            
            
            Calendar cal=Calendar.getInstance();
            String fileName = "thongkekhac_BAC_" + Utilities.parseDateTime(cal.getTime(), "dd_MM_yyyy") + ".xls";
            File f = new File(getLocalPath() + File.separator + fileName);
            try (FileOutputStream fileOut = FileUtils.openOutputStream(f, true)) {
                wb.write(fileOut);
                fileOut.close();
            }
            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext().getContext())
                    .getResourceAsStream(getUrlUploadFiles() + "/" + fileName);
            String contentType = FacesContext.getCurrentInstance().getExternalContext().getMimeType(getLocalPath() + File.separator + fileName);
            return new DefaultStreamedContent(stream, contentType, fileName);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(ReportEcxelController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public String getLocalPath() {
        ExternalContext context = FacesContext.getCurrentInstance().getExternalContext();
        return context.getRealPath(context.getInitParameter(ConstClass.UPLOAD_FILE_PATH))
                + File.separator + "Report";
    }

    public String getUrlUploadFiles() {
        return FacesContext.getCurrentInstance().getExternalContext().getInitParameter(ConstClass.UPLOAD_FILE_PATH)
                + "/" + "Report";
    }

    /**
     *
     * @param cell
     * @param numRow
     * @return current cell index
     */
    public static int mergeRowspan(Cell cell, int numRow) {
        Row row = cell.getRow();
        CellRangeAddress address = new CellRangeAddress(
                row.getRowNum(),
                row.getRowNum() + numRow,
                cell.getColumnIndex(),
                cell.getColumnIndex());
        RegionUtil.setBorderTop((short) 1, address, cell.getSheet(), cell.getSheet().getWorkbook());
        RegionUtil.setBorderRight((short) 1, address, cell.getSheet(), cell.getSheet().getWorkbook());
        RegionUtil.setBorderBottom((short) 1, address, cell.getSheet(), cell.getSheet().getWorkbook());
        RegionUtil.setBorderLeft((short) 1, address, cell.getSheet(), cell.getSheet().getWorkbook());
        row.getSheet().addMergedRegion(address);
        return row.getRowNum() + numRow;
    }

    /**
     *
     * @param cell
     * @param numRow
     * @return current cell index
     */
    public static int mergeRowspanWithBoder(Cell cell, int numRow) {
        Row row = cell.getRow();
        CellRangeAddress address = new CellRangeAddress(
                row.getRowNum(),
                row.getRowNum() + numRow,
                cell.getColumnIndex(),
                cell.getColumnIndex());
        RegionUtil.setBorderTop(CellStyle.BORDER_THIN, address, cell.getSheet(), cell.getSheet().getWorkbook());
        RegionUtil.setBorderRight(CellStyle.BORDER_THIN, address, cell.getSheet(), cell.getSheet().getWorkbook());
        RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, address, cell.getSheet(), cell.getSheet().getWorkbook());
        RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, address, cell.getSheet(), cell.getSheet().getWorkbook());
        row.getSheet().addMergedRegion(address);
        return row.getRowNum() + numRow;
    }

    /**
     *
     * @param cell
     * @param numColumn
     * @return current cell index
     */
    public static int mergeColspan(Cell cell, int numColumn) {
        Row row = cell.getRow();
        CellRangeAddress address = new CellRangeAddress(row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex() + numColumn);
        RegionUtil.setBorderTop(CellStyle.BORDER_THIN, address, cell.getSheet(), cell.getSheet().getWorkbook());
        RegionUtil.setBorderRight(CellStyle.BORDER_THIN, address, cell.getSheet(), cell.getSheet().getWorkbook());
        RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, address, cell.getSheet(), cell.getSheet().getWorkbook());
        RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, address, cell.getSheet(), cell.getSheet().getWorkbook());
        row.getSheet().addMergedRegion(address);
        return cell.getColumnIndex() + numColumn;
    }

    /**
     *
     * @param cell
     * @param numColumn
     * @return current cell index
     */
    public static int mergeColspanNoBoder(Cell cell, int numColumn) {
        Row row = cell.getRow();
        CellRangeAddress address = new CellRangeAddress(row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex() + numColumn);
        row.getSheet().addMergedRegion(address);
        return cell.getColumnIndex() + numColumn;
    }
}
